﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using HMS.BL;
using HMS.Common;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Collections.Specialized;
using HMS.Security;
using HMS.Logging;
using System.Xml;
using System.Xml.Serialization;
using System.IO;

namespace HMS.DL
{

    public class ConfigData
    {

        public string ClsName = "$ConfigData$";
        public string TableName { get; set; }
        public int ListId { get; set; }
        public string ListName { get; set; }

        public int ListColId { get; set; }
        public int id { get; set; }
        public int MasterId { get; set; }
        //  public coll reqformVal{ get; set; }
        public NameValueCollection allkeys { get; set; }
        public string srcVal { get; set; }
        public string sidx { get; set; }
        public string sord { get; set; }
        public int rows { get; set; }
        public int page { get; set; }
        public int limit { get; set; }
        public string hidColList { get; set; }
        public int UserTypeId { get; set; }
        public int UserId { get; set; }
        public string query { get; set; }
        public bool _search { get; set; }

        public string GetXMasterListColumns()
        {
            try
            {

                string primarykey = string.Empty;

                Connection sqlConn = new Connection();

                Util.WritInfoLog("$ConfigData$", "GetXMasterListColumns", "procedure for GetXMasterListColumns", "usp_GetConfigXMasterList");
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetConfigXMasterList");
                Util.WritInfoLog("$ConfigData$", "GetXMasterListColumns", "End procedure for GetXMasterListColumns");

                Grid griddata = new Grid();
                //set columns,colmodel,primary key
                griddata = SetColModel(griddata, ds.Tables[1]);

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(griddata);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "GetXMasterListdata", "Error in GetXMasterListdata", "", ex);
                throw ex;
            }
        }

        public string GetXMasterListdata()
        {
            try
            {

                Connection sqlConn = new Connection();

                //string strQry = " SELECT LIST_ID,LIST_NAME,TABLE_ABBR, ";
                //strQry = strQry + " IsTableCreated = ";
                //strQry = strQry + " CASE WHEN OBJECT_ID(TABLE_XREF) IS NULL THEN 0 ";
                //strQry = strQry + " ELSE 1 END  ";
                //strQry = strQry + " FROM X_MASTER_LIST ";
                //strQry = strQry + " ORDER BY LIST_ID ASC ";

                if (sidx == "" || sidx == null)
                {
                    sidx = " LIST_ID ";
                }

                string sortExp = sidx + " " + sord;
                int start = ((page - 1) * rows);

                //before SP
                //string strQry = " SELECT LIST_ID,LIST_NAME,TABLE_ABBR, ";
                //strQry = strQry + " IsTableCreated = ";
                //strQry = strQry + " CASE WHEN OBJECT_ID(TABLE_XREF) IS NULL THEN 0 ";
                //strQry = strQry + " ELSE 1 END  ";
                //strQry = strQry + " FROM (SELECT ROW_NUMBER() OVER (ORDER BY " + sortExp + ") AS Row,";
                //strQry = strQry + " * FROM  X_MASTER_LIST ";
                ////for search filter
                //if (_search && (query != null || query != ""))
                //{
                //    strQry = strQry + " WHERE " + query.Replace("\"", "'");//errString.Replace("docment", "document");
                //}
                //strQry = strQry + ") tmp ";
                //strQry = strQry + " WHERE Row > " + start + " AND Row <= " + (start + rows);

                //strQry = strQry + " SELECT COUNT(*) CNT FROM X_MASTER_LIST ";
                ////for search filter
                //if (_search && (query != null || query != ""))
                //{
                //    strQry = strQry + " WHERE " + query.Replace("\"", "'");//errString.Replace("docment", "document");
                //}              

                //Util.WritInfoLog("$ConfigData$", "GetXMasterListdata", "Query for GetXMasterListdata", string.Format("Query:{0}", strQry));
                //DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
                //Util.WritInfoLog("$ConfigData$", "GetXMasterListdata", "End Query for GetXMasterListdata");

                //////////By SP
                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@start", DbType.Int32, ParameterDirection.Input, 0, start));
                lstparam.Add(new DBParameter("@rows", DbType.Int32, ParameterDirection.Input, 0, rows));
                lstparam.Add(new DBParameter("@query", DbType.String, ParameterDirection.Input, 1000, query.Replace("\"", "'")));
                lstparam.Add(new DBParameter("@sortExp", DbType.String, ParameterDirection.Input, 1000, sortExp));
                lstparam.Add(new DBParameter("@_search", DbType.Boolean, ParameterDirection.Input, 0, _search));

                Util.WritInfoLog(ClsName, "GetXMasterListdata", "method for GetXMasterListdata");
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetXMasterListdata", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetXMasterListdata", "End method for GetXMasterListdata");
                ///////

                DataTable dt = ds.Tables[0];

                int cnt = 0;
                cnt = Convert.ToInt32(ds.Tables[1].Rows[0]["CNT"]);
                string strData = Common.JsonForJqgrid(ds.Tables[0], rows, cnt, page);

                return strData;
            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "GetXMasterListdata", "Error in GetXMasterListdata", "", ex);
                throw ex;
            }
        }

        public string AddXMasterList()
        {
            try
            {

                Connection sqlConn = new Connection();

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@TABLE_ABBR", DbType.String, ParameterDirection.Input, 255, allkeys["TABLE_ABBR"]));
                lstparam.Add(new DBParameter("@LIST_NAME", DbType.String, ParameterDirection.Input, 255, allkeys["LIST_NAME"]));

                Util.WritInfoLog(ClsName, "AddXMasterList", "get count method for AddXMasterList", "usp_GetXMasterListCount");
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetXMasterListCount", ref(lstparam));
                Util.WritInfoLog(ClsName, "AddXMasterList", "End get count method for AddXMasterList");

                //before SP
                //get max + 1  id -1 
                //string strQry = " SELECT MAX(LIST_ID)+ 1 AS LIST_ID FROM X_MASTER_LIST";

                //strQry = strQry + " SELECT COUNT(*) CNT FROM X_MASTER_LIST WHERE LIST_NAME='";
                //strQry = strQry + allkeys["LIST_NAME"] + "'";
                //strQry = strQry + " OR TABLE_ABBR='" + allkeys["TABLE_ABBR"] + "'";

                //Util.WritInfoLog("$ConfigData$", "AddXMasterList", "Query for getting details in AddXMasterList", string.Format("Query:{0}", strQry));
                //DataSet dsMax = sqlConn.GetDataSetFromFactory(strQry);
                //Util.WritInfoLog("$ConfigData$", "AddXMasterList", "End Query for getting details in AddXMasterList");

                if (Convert.ToInt16(ds.Tables[0].Rows[0]["CNT"]) > 0)
                {
                    return "false| List Name AND ABBR should be unique !";
                }
                else
                {

                    //before SP
                    // string strInsert = "INSERT INTO X_MASTER_LIST";
                    // strInsert = strInsert + "(LIST_NAME,TABLE_NAME,TABLE_ABBR,TABLE_XREF,TABLE_GREY,";
                    // strInsert = strInsert + "TABLE_HISTORY,TABLE_SOURCE,TABLE_XREF_TEMP,TABLE_XREF_TEMPDETAIL) VALUES (";
                    //// strInsert = strInsert + dsMax.Tables[0].Rows[0][0] + ",";
                    // strInsert = strInsert + "'" + allkeys["LIST_NAME"] + "',";
                    // strInsert = strInsert + "'M_" + allkeys["TABLE_ABBR"].ToString().ToUpper() + "',";
                    // strInsert = strInsert + "'" + allkeys["TABLE_ABBR"] + "',";
                    // strInsert = strInsert + "'M_" + allkeys["TABLE_ABBR"].ToString().ToUpper() + "_XREF',";
                    // strInsert = strInsert + "'M_" + allkeys["TABLE_ABBR"].ToString().ToUpper() + "_GREY',";
                    // strInsert = strInsert + "'M_" + allkeys["TABLE_ABBR"].ToString().ToUpper() + "_HISTORY',";
                    // strInsert = strInsert + "'M_" + allkeys["TABLE_ABBR"].ToString().ToUpper() + "_SOURCE',";
                    // strInsert = strInsert + "'M_" + allkeys["TABLE_ABBR"].ToString().ToUpper() + "_XREF_TEMP',";
                    // strInsert = strInsert + "'M_" + allkeys["TABLE_ABBR"].ToString().ToUpper() + "_XREF_TEMPDETAIL'";
                    // strInsert = strInsert + ")";

                    // JavaScriptSerializer js = new JavaScriptSerializer();

                    // Util.WritInfoLog("$ConfigData$", "AddXMasterList", "Query for AddXMasterList", string.Format("Query:{0}", strInsert));
                    // Int32 res = sqlConn.SaveDataByFactory(strInsert);
                    // Util.WritInfoLog("$ConfigData$", "AddXMasterList", "End Query for AddXMasterList");

                    // if (res == 1) return dsMax.Tables[0].Rows[0][0].ToString();
                    // else return "false";

                    List<DBParameter> lstparam1 = new List<DBParameter>();
                    lstparam1.Add(new DBParameter("@TABLE_ABBR", DbType.String, ParameterDirection.Input, 255, allkeys["TABLE_ABBR"]));
                    lstparam1.Add(new DBParameter("@LIST_NAME", DbType.String, ParameterDirection.Input, 255, allkeys["LIST_NAME"]));
                    lstparam1.Add(new DBParameter("@RET", DbType.Int32, ParameterDirection.Output, 0, 0));

                    Util.WritInfoLog(ClsName, "AddXMasterList", "method for AddXMasterList", "usp_AddXMasterList");
                    Int32 res = sqlConn.SaveDataByFactoryByProc("usp_AddXMasterList", ref(lstparam1));
                    Util.WritInfoLog(ClsName, "AddXMasterList", "End method for AddXMasterList");

                    Int32 ident = 0;
                    foreach (DBParameter dbparam in lstparam1)
                    {
                        if (dbparam.name == "@RET")
                        {
                            if (dbparam.value != DBNull.Value)
                            {
                                ident = Convert.ToInt32(dbparam.value);
                                break;
                            }
                        }
                    }

                    return Convert.ToString(ident);
                    //else return "false";

                }
            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "AddXMasterList", "Error in AddXMasterList", "", ex);
                return "false";
                throw ex;
            }
        }

        public string EditXMasterList()
        {
            try
            {

                Connection sqlConn = new Connection();

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@TABLE_ABBR", DbType.String, ParameterDirection.Input, 255, allkeys["TABLE_ABBR"]));
                lstparam.Add(new DBParameter("@LIST_NAME", DbType.String, ParameterDirection.Input, 255, allkeys["LIST_NAME"]));
                lstparam.Add(new DBParameter("@LIST_ID", DbType.Int32, ParameterDirection.Input, 255, ListId));

                Util.WritInfoLog(ClsName, "EditXMasterList", "get count method for EditXMasterList", "usp_GetXMasterListCountEdit");
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetXMasterListCountEdit", ref(lstparam));
                Util.WritInfoLog(ClsName, "EditXMasterList", "End get count method for EditXMasterList");

                //before SP
                ////get table names from X_MASTER_LIST 
                //string strQry = " SELECT * FROM X_MASTER_LIST ";
                //strQry = strQry + " WHERE LIST_ID =" + ListId;

                //strQry = strQry + " SELECT COUNT(*) CNT FROM X_MASTER_LIST WHERE LIST_NAME='";
                //strQry = strQry + allkeys["LIST_NAME"] + "'";
                //strQry = strQry + " OR TABLE_ABBR='" + allkeys["TABLE_ABBR"] + "'";
                //strQry = strQry + " AND LIST_ID <>" + ListId;

                //Util.WritInfoLog("$ConfigData$", "EditXMasterList", "Query for getting details in EditXMasterList", string.Format("Query:{0}", strQry));
                //DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
                //Util.WritInfoLog("$ConfigData$", "EditXMasterList", "End Query for getting details in EditXMasterList");

                DataTable dt = ds.Tables[0];
                DataView Dv = new DataView(dt);

                if (Convert.ToInt16(ds.Tables[0].Rows[0]["CNT"]) > 0)
                {
                    return "false| List Name And Abbr should be unique !";
                }
                else
                {


                    List<DBParameter> lstparam1 = new List<DBParameter>();
                    lstparam1.Add(new DBParameter("@LIST_NAME", DbType.String, ParameterDirection.Input, 255, allkeys["LIST_NAME"]));
                    lstparam1.Add(new DBParameter("@LIST_ID", DbType.Int32, ParameterDirection.Input, 0, ListId));
                    lstparam.Add(new DBParameter("@returnValue", DbType.Int16, ParameterDirection.ReturnValue));

                    Util.WritInfoLog(ClsName, "EditXMasterList", "method for EditXMasterList", "usp_EditXMasterList");
                    Int32 res1 = sqlConn.SaveDataByFactoryByProc("usp_EditXMasterList", ref(lstparam1));
                    Util.WritInfoLog(ClsName, "EditXMasterList", "End method for EditXMasterList");

                    int retVal = 0;

                    foreach (DBParameter dbparam in lstparam)
                    {
                        if (dbparam.name == "@returnValue")
                        {
                            retVal = Convert.ToInt32(dbparam.value);
                            break;
                        }
                    }

                    if (retVal == 0) return "false";
                    else return "true";

                   // if (res1 == 1) return "true"; else return "false";

                    //before SP
                    //string strUpdate = "UPDATE X_MASTER_LIST SET ";
                    //strUpdate = strUpdate + "LIST_NAME=";
                    //strUpdate = strUpdate + " '" + allkeys["LIST_NAME"] + "'";

                    ////strUpdate = strUpdate + " '" + allkeys["LIST_NAME"] + "',";

                    ////strUpdate = strUpdate + "TABLE_NAME=";
                    ////strUpdate = strUpdate + " 'M_" + allkeys["TABLE_ABBR"] + "',";

                    ////strUpdate = strUpdate + "TABLE_ABBR=";
                    ////strUpdate = strUpdate + "  '" + allkeys["TABLE_ABBR"] + "',";

                    ////strUpdate = strUpdate + "TABLE_XREF=";
                    ////strUpdate = strUpdate + "  'M_" + allkeys["TABLE_ABBR"] + "_XREF',";

                    ////strUpdate = strUpdate + "TABLE_GREY=";
                    ////strUpdate = strUpdate + "  'M_" + allkeys["TABLE_ABBR"] + "_GREY',";
                    ////strUpdate = strUpdate + "TABLE_HISTORY=";
                    ////strUpdate = strUpdate + " 'M_" + allkeys["TABLE_ABBR"] + "_HISTORY'";

                    ////where condition
                    //strUpdate = strUpdate + " WHERE LIST_ID=" + ListId;

                    //Util.WritInfoLog("$ConfigData$", "EditXMasterList", "Query for EditXMasterList", string.Format("Query:{0}", strUpdate));
                    //Int32 res = sqlConn.SaveDataByFactory(strUpdate);
                    //Util.WritInfoLog("$ConfigData$", "EditXMasterList", "End Query for EditXMasterList");

                    ////strQry = "SELECT COUNT(*) AS CNT FROM sys.objects where name=('" + dt.Rows[0]["TABLE_NAME"] + "')";
                    ////strQry = strQry + "SELECT COUNT(*) AS CNT FROM sys.objects where name=('" + dt.Rows[0]["TABLE_XREF"] + "')";
                    ////strQry = strQry + "SELECT COUNT(*) AS CNT FROM sys.objects where name=('" + dt.Rows[0]["TABLE_GREY"] + "')";
                    ////strQry = strQry + "SELECT COUNT(*) AS CNT FROM sys.objects where name=('" + dt.Rows[0]["TABLE_HISTORY"] + "')";

                    ////DataSet dsEx = sqlConn.GetDataSetFromFactory(strQry);
                    //////DataTable dtexist=dsExist.Tables[0]

                    //////rename tables
                    ////if (Convert.ToInt32(dsEx.Tables[0].Rows[0]["CNT"]) > 0)
                    ////{
                    ////    strUpdate = "sp_RENAME '" + dt.Rows[0]["TABLE_NAME"] + "' ," + " 'M_" + allkeys["TABLE_ABBR"] + "'";
                    ////    res = sqlConn.SaveDataByFactory(strUpdate);
                    ////}

                    //////strUpdate = "IF OBJECT_ID('" + dt.Rows[0]["TABLE_XREF"] + "') IS NOT NULL ";
                    ////if (Convert.ToInt32(dsEx.Tables[1].Rows[0]["CNT"]) > 0)
                    ////{
                    ////    strUpdate = "sp_RENAME '" + dt.Rows[0]["TABLE_XREF"] + "' ," + " 'M_" + allkeys["TABLE_ABBR"] + "_XREF'";
                    ////    res = sqlConn.SaveDataByFactory(strUpdate);
                    ////}

                    ////if (Convert.ToInt32(dsEx.Tables[2].Rows[0]["CNT"]) > 0)
                    ////{
                    ////    //strUpdate = "IF OBJECT_ID('" + dt.Rows[0]["TABLE_GREY"] + "') IS NOT NULL ";
                    ////    strUpdate = "sp_RENAME '" + dt.Rows[0]["TABLE_GREY"] + "' ," + " 'M_" + allkeys["TABLE_ABBR"] + "_GREY'";
                    ////    res = sqlConn.SaveDataByFactory(strUpdate);
                    ////}

                    ////if (Convert.ToInt32(dsEx.Tables[3].Rows[0]["CNT"]) > 0)
                    ////{
                    ////    //strUpdate = "IF OBJECT_ID('" + dt.Rows[0]["TABLE_HISTORY"] + "') IS NOT NULL ";
                    ////    strUpdate = "sp_RENAME '" + dt.Rows[0]["TABLE_HISTORY"] + "' ," + " 'M_" + allkeys["TABLE_HISTORY"] + "_HISTORY'";
                    ////    res = sqlConn.SaveDataByFactory(strUpdate);
                    ////}


                }

                //if (res == 1) return "true";
                //else return "false";
                // return res.ToString();
                //return js.Serialize(new Records(sqlConn.ExecuteCommand(strUpdate)));

            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "EditXMasterList", "Error in EditXMasterList", "", ex);
                return "false";
                throw ex;
            }
        }

        public string DelXMasterList()
        {
            try
            {

                Connection sqlConn = new Connection();

                ////get primary key column
                //string strQry = " SELECT TABLE_NAME,TABLE_XREF,TABLE_GREY,TABLE_HISTORY FROM X_MASTER_LIST ";
                //strQry = strQry + " WHERE LIST_ID =" + ListId;

                //Util.WritInfoLog("$ConfigData$", "DelXMasterList", "Query for getting details in DelXMasterList", string.Format("Query:{0}", strQry));
                //DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
                //Util.WritInfoLog("$ConfigData$", "DelXMasterList", "End Query for getting details in DelXMasterList");

                //DataTable dt = ds.Tables[0];

                //check validity 
                //bool bdel = ValidateXMListDelete(ds.Tables[1].Rows[0]["TABLE_NAME"].ToString(), ds.Tables[0].Rows[0]["FIELD_NAME"].ToString());

                bool bdel = true;

                if (bdel == true)
                {

                    List<DBParameter> lstparam = new List<DBParameter>();
                    lstparam.Add(new DBParameter("@LIST_ID", DbType.Int32, ParameterDirection.Input, 0, ListId));
                    lstparam.Add(new DBParameter("@returnValue", DbType.Int16, ParameterDirection.ReturnValue));

                    Util.WritInfoLog(ClsName, "DelXMasterList", "method for DelXMasterList", "usp_DeleteXMasterList");
                    Int32 res = sqlConn.SaveDataByFactoryByProc("usp_DeleteXMasterList", ref(lstparam));
                    Util.WritInfoLog(ClsName, "DelXMasterList", "End method for DelXMasterList");

                    int retVal = 0;

                    foreach (DBParameter dbparam in lstparam)
                    {
                        if (dbparam.name == "@returnValue")
                        {
                            retVal = Convert.ToInt32(dbparam.value);
                            break;
                        }
                    }

                    if (retVal == 0) return "false"; else return "true";

                    //if (res == 1) return "true"; else return "false";
                    //before SP
                    //string strDelQry = " DELETE FROM X_MASTER_LIST_COLUMNS";
                    //strDelQry = strDelQry + " WHERE LIST_ID=" + ListId;

                    //Util.WritInfoLog("$ConfigData$", "DelXMasterList", "Query for delete in X_MASTER_LIST_COLUMNS", string.Format("Query:{0}", strDelQry));
                    //res = sqlConn.SaveDataByFactory(strDelQry);
                    //Util.WritInfoLog("$ConfigData$", "DelXMasterList", "End Query for delete in X_MASTER_LIST_COLUMNS");

                    ////string strUpdQry = " UPDATE X_MASTER_LIST ";
                    ////strUpdQry = strUpdQry + " SET IS_ACTIVE='False' ";
                    ////strUpdQry = strUpdQry + " WHERE LIST_ID=" + ListId;
                    ////res = sqlConn.SaveDataByFactory(strUpdQry);

                    //string strDelQry1 = " DELETE FROM X_MASTER_LIST";
                    //strDelQry1 = strDelQry1 + " WHERE LIST_ID=" + ListId;

                    //Util.WritInfoLog("$ConfigData$", "DelXMasterList", "Query for delete in X_MASTER_LIST", string.Format("Query:{0}", strDelQry1));
                    //res = sqlConn.SaveDataByFactory(strDelQry1);
                    //Util.WritInfoLog("$ConfigData$", "DelXMasterList", "End Query for delete in X_MASTER_LIST");


                    //strDelQry = "IF OBJECT_ID('" + dt.Rows[0]["TABLE_NAME"] + "') IS NOT NULL ";
                    //strDelQry = strDelQry + "DROP TABLE " + dt.Rows[0]["TABLE_NAME"];
                    //res = sqlConn.SaveDataByFactory(strDelQry);

                    //strDelQry = "IF OBJECT_ID('" + dt.Rows[0]["TABLE_XREF"] + "') IS NOT NULL ";
                    //strDelQry = strDelQry + "DROP TABLE " + dt.Rows[0]["TABLE_XREF"];
                    //res = sqlConn.SaveDataByFactory(strDelQry);

                    //strDelQry = "IF OBJECT_ID('" + dt.Rows[0]["TABLE_GREY"] + "') IS NOT NULL ";
                    //strDelQry = strDelQry + "DROP TABLE " + dt.Rows[0]["TABLE_GREY"];
                    //res = sqlConn.SaveDataByFactory(strDelQry);

                    //strDelQry = "IF OBJECT_ID('" + dt.Rows[0]["TABLE_HISTORY"] + "') IS NOT NULL ";
                    //strDelQry = strDelQry + "DROP TABLE " + dt.Rows[0]["TABLE_HISTORY"];
                    //res = sqlConn.SaveDataByFactory(strDelQry);

                    //return "true";

                }
                else
                {
                    return "Can't Dlete data in use.";
                }
            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "DelXMasterList", "Error in DelXMasterList", "", ex);
                throw ex;
            }
        }

        public string GetXMasterListColColumnsdata()
        {
            try
            {

                string primarykey = string.Empty;
                Connection sqlConn = new Connection();

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@LIST_ID", DbType.Int32, ParameterDirection.Input, 0, ListId));

                Util.WritInfoLog(ClsName, "GetXMasterListColColumnsdata", "method for GetXMasterListColColumnsdata", "usp_GetXMasterListColColumns");
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetXMasterListColColumns", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetXMasterListColColumnsdata", "End method for GetXMasterListColColumnsdata");


                //before SP
                //string strQry = " SELECT * FROM X_MASTER_LIST_COLUMNS ";
                //strQry = strQry + " WHERE LIST_ID = " + ListId;
                //strQry = strQry + " AND IS_ACTIVE = 'True' ";
                //strQry = strQry + " ORDER BY LIST_COL_ID ASC ";

                //strQry = strQry + "    SELECT sys.columns.name as DISPLAY, ";
                //strQry = strQry + "     sys.columns.name as FIELD_NAME ,sys.types.name DATA_TYPE, ";
                //strQry = strQry + "   sys.columns.max_length PRECISION,  ";
                //strQry = strQry + "    IS_REQUIRED= CASE sys.columns.is_nullable WHEN 1 THEN 0  ELSE 1 END ";
                //strQry = strQry + "   ,ISNULL(i.is_primary_key, 0)IS_PRIMARY,sys.columns.is_nullable IS_NULLABLE, ";
                //strQry = strQry + "  sys.columns.is_identity FROM sys.columns  ";
                //strQry = strQry + "   INNER JOIN sys.types ON sys.columns.system_type_id ";
                //strQry = strQry + "   =sys.types.system_type_id ";
                //strQry = strQry + "   LEFT OUTER JOIN    sys.index_columns ic ON ic.object_id ";
                //strQry = strQry + "    = sys.columns.object_id  AND ic.column_id = sys.columns.column_id  ";
                //strQry = strQry + "    LEFT OUTER JOIN     sys.indexes i ON ic.object_id = i.object_id ";
                //strQry = strQry + "    AND ic.index_id = i.index_id  WHERE sys.columns.object_id ";
                //strQry = strQry + "   = OBJECT_ID('X_MASTER_LIST_COLUMNS') ";
                //strQry = strQry + " order by sys.columns.column_id ";

                ////find if table_name exists
                ////strQry = strQry + " SELECT COUNT(*) FROM sys.objects WHERE object_id = OBJECT_ID(' ";
                //strQry = strQry + " SELECT TABLE_NAME from X_MASTER_LIST where LIST_ID =" + ListId;

                //Util.WritInfoLog("$ConfigData$", "GetXMasterListColColumnsdata", "Query for GetXMasterListColColumnsdata", string.Format("Query:{0}", strQry));
                //DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
                //Util.WritInfoLog("$ConfigData$", "GetXMasterListColColumnsdata", "End Query GetXMasterListColColumnsdata");

                //strQry = " SELECT COUNT(*) CNT FROM sys.objects WHERE object_id = OBJECT_ID('";
                //strQry = strQry + ds.Tables[2].Rows[0]["TABLE_NAME"] + "')";

                //Util.WritInfoLog("$ConfigData$", "GetXMasterListColColumnsdata", "Query for getting count in table_name", string.Format("Query:{0}", strQry));
                //DataSet dsExist = sqlConn.GetDataSetFromFactory(strQry);
                //Util.WritInfoLog("$ConfigData$", "GetXMasterListColColumnsdata", "End Query for getting count in table_name");


                DataTable dtcol = ds.Tables[1];
                DataView dv = new DataView(dtcol);

                dv.RowFilter = " FIELD_NAME <> 'LIST_ID'";
                DataTable dtfilter = dv.ToTable();

                Grid griddata = new Grid();
                //set columns,colmodel,primary key
                griddata = SetColModel(griddata, dtfilter);

                //List<object> rows = SetDatarow(dt);
                //griddata.datarow = rows;

                //Convert.ToString(dsExist.Tables[0].Rows[0]["CNT"]) + "#" +
                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(griddata) + "|" + Convert.ToString(ds.Tables[2].Rows[0]["CNT"]);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "GetXMasterListColdata", "Error in GetXMasterListColdata", "", ex);
                throw ex;
            }
        }

        public string GetXMasterListColdata()
        {
            try
            {

                string primarykey = string.Empty;
                Connection sqlConn = new Connection();

                if (sidx == "" || sidx == null)
                {
                    sidx = " LIST_ID ";
                }
                // sidx = " LIST_ID ASC " + sidx ;

                //string sortExp = " LIST_ID , " + sidx + " " + sord;
                string sortExp = sidx + " " + sord;
                int start = ((page - 1) * rows);

                List<DBParameter> lstparam = new List<DBParameter>();

                lstparam.Add(new DBParameter("@LIST_ID", DbType.Int32, ParameterDirection.Input, 0, ListId));
                lstparam.Add(new DBParameter("@query", DbType.String, ParameterDirection.Input, 1000, query.Replace("\"", "'")));
                lstparam.Add(new DBParameter("@_search", DbType.Boolean, ParameterDirection.Input, 0, _search));

                Util.WritInfoLog(ClsName, "GetXMasterListColColumnsdata", "method for GetXMasterListColdata", "usp_GetXMasterListColumn");
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetXMasterListColumn", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetXMasterListColColumnsdata", "End method for GetXMasterListColdata");

                ////before SP
                //string strQry = " SELECT * FROM X_MASTER_LIST_COLUMNS ";
                //strQry = strQry + " WHERE LIST_ID = " + ListId;
                //strQry = strQry + " AND IS_ACTIVE = 'True' ";
                ////for search filter
                //if (_search && (query != null || query != ""))
                //{
                //    strQry = strQry + " AND " + query.Replace("\"", "'");//errString.Replace("docment", "document");
                //}
                //strQry = strQry + " ORDER BY " + sortExp;

                //strQry = strQry + " SELECT COUNT(*) CNT FROM X_MASTER_LIST_COLUMNS ";
                //strQry = strQry + " WHERE LIST_ID = " + ListId;
                ////for search filter
                //if (_search && (query != null || query != ""))
                //{
                //    strQry = strQry + " AND " + query.Replace("\"", "'");//errString.Replace("docment", "document");
                //}

                //Util.WritInfoLog("$ConfigData$", "GetXMasterListColdata", "Query for GetXMasterListColdata", string.Format("Query:{0}", strQry));
                //DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
                //Util.WritInfoLog("$ConfigData$", "GetXMasterListColdata", "End Query for GetXMasterListColdata");

                DataTable dt = ds.Tables[0];
                dt.TableName = "X_MASTER_LIST_COLUMNS";

                int cnt = 0;
                cnt = Convert.ToInt32(ds.Tables[1].Rows[0]["CNT"]);
                dt.Columns.Remove("LIST_ID");
                string strData = Common.JsonForJqgrid(dt, rows, cnt, page);

                return strData;
            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "GetXMasterListColdata", "Error in GetXMasterListColdata", "", ex);
                throw ex;
            }
        }


        public string AddXMasterListCol()
        {
            try
            {

                Connection sqlConn = new Connection();
                List<dict> allkey = new List<dict>();

                foreach (string key in allkeys)
                {
                    dict d = new dict();
                    d.key1 = key;
                    d.value = allkeys[key];
                    allkey.Add(d);
                }

                var xmlDocument = new XmlDocument();
                var serializer = new XmlSerializer(typeof(List<dict>));

                using (var stream = new MemoryStream())
                {
                    serializer.Serialize(stream, allkey);
                    stream.Flush(); stream.Seek(0, SeekOrigin.Begin);
                    xmlDocument.Load(stream);
                }

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@field_Xml", DbType.Xml, ParameterDirection.Input, 0, xmlDocument.OuterXml));
                lstparam.Add(new DBParameter("@ListId", DbType.Int32, ParameterDirection.Input, 0, ListId));

                Util.WritInfoLog(ClsName, "AddXMasterListCol", "method for AddXMasterListCol", "usp_addXMasterListCol");
                Int32 res = Convert.ToInt32(sqlConn.SaveDataByFactoryByProc("usp_addXMasterListCol", ref(lstparam)));
                Util.WritInfoLog(ClsName, "AddXMasterListCol", "End method for AddXMasterListCol");

                if (res == 0) return "false";
                else return Convert.ToString(res);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "AddXMasterListCol", "Error in AddXMasterListCol", "", ex);
                throw ex;
            }
        }

        //original before SP
        //public string AddXMasterListCol()
        //{
        //    try
        //    {

        //        Connection sqlConn = new Connection();

        //        string strQry = "  SELECT sys.types.name DATA_TYPE,sys.columns.name as FIELD_NAME ,";
        //        strQry = strQry + "ISNULL(i.is_primary_key, 0) IS_PRIMARY ";
        //        strQry = strQry + " FROM sys.columns  ";
        //        strQry = strQry + " INNER JOIN sys.types ";
        //        strQry = strQry + " ON sys.columns.system_type_id =sys.types.system_type_id	";
        //        strQry = strQry + "LEFT OUTER JOIN    sys.index_columns ic ON ic.object_id = sys.columns.object_id ";
        //        strQry = strQry + " AND ic.column_id = sys.columns.column_id ";
        //        strQry = strQry + "LEFT OUTER JOIN     sys.indexes i ON ic.object_id = i.object_id ";
        //        strQry = strQry + "AND ic.index_id = i.index_id ";
        //        strQry = strQry + "WHERE sys.columns.object_id = OBJECT_ID('X_MASTER_LIST_COLUMNS')";

        //        //get max + 1  id -1
        //        strQry = strQry + " SELECT MAX(LIST_COL_ID)+ 1 AS LIST_ID FROM X_MASTER_LIST_COLUMNS";

        //        strQry = strQry + " SELECT * FROM X_MASTER_LIST";
        //        strQry = strQry + " WHERE LIST_ID=" + ListId;

        //        Util.WritInfoLog("$ConfigData$", "AddXMasterListCol", "Query for getting details in AddXMasterListCol", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog("$ConfigData$", "AddXMasterListCol", "End Query for getting details in AddXMasterListCol");

        //        DataTable dtcol = ds.Tables[0];
        //        DataView Dv = new DataView(dtcol);

        //        Dv.RowFilter = " IS_PRIMARY = FALSE AND FIELD_NAME <> 'LIST_ID'";

        //        string strInsert = "INSERT INTO X_MASTER_LIST_COLUMNS";

        //        string colNm = string.Empty;
        //        string colVl = string.Empty;

        //        foreach (DataRowView dr in Dv)
        //        {

        //            colNm = colNm + dr["FIELD_NAME"].ToString() + ",";

        //            if (dr["DATA_TYPE"].ToString().ToLower() == "varchar" || dr["DATA_TYPE"].ToString().ToLower() == "nvarchar" || dr["DATA_TYPE"].ToString().ToLower() == "char")
        //            {
        //                colVl = colVl + "'" + allkeys[dr["FIELD_NAME"].ToString()] + "'" + ",";
        //            }
        //            else if (dr["DATA_TYPE"].ToString().ToLower() == "date")
        //            {
        //                colVl = colVl + "'" + Convert.ToDateTime(allkeys[dr["FIELD_NAME"].ToString()]).ToString("MM/dd/yyyy") + "',"; // Convert.ToDateTime(EndDate).ToString("MM/dd/yyyy")
        //            }
        //            else if (dr["DATA_TYPE"].ToString().ToLower() == "number" || dr["DATA_TYPE"].ToString().ToLower() == "float" || dr["DATA_TYPE"].ToString().ToLower() == "int")
        //            {
        //                if (allkeys[dr["FIELD_NAME"].ToString()] == "")
        //                {
        //                    colVl = colVl + "null,";
        //                }
        //                else
        //                {
        //                    colVl = colVl + allkeys[dr["FIELD_NAME"].ToString()] + ",";
        //                }
        //                //colVl = colVl + allkeys[dr["FIELD_NAME"].ToString()] + ",";
        //            }
        //            else if (dr["DATA_TYPE"].ToString().ToLower() == "bit")
        //            {

        //                if (allkeys[dr["FIELD_NAME"].ToString()] == "Yes")
        //                {
        //                    colVl = colVl + "1,";
        //                }
        //                else
        //                {
        //                    colVl = colVl + "0,";
        //                }
        //            }
        //        }

        //        colNm = colNm.Substring(0, colNm.Length - 1);
        //        colVl = colVl.Substring(0, colVl.Length - 1);

        //        Dv.RowFilter = " IS_PRIMARY = TRUE ";

        //        strInsert = strInsert + "(" + Dv[0]["FIELD_NAME"].ToString() + "," + "LIST_ID," + colNm + ")";
        //        strInsert = strInsert + "VALUES (" + ds.Tables[1].Rows[0][0] + "," + ListId + "," + colVl + ")";

        //        JavaScriptSerializer js = new JavaScriptSerializer();

        //        Util.WritInfoLog("$ConfigData$", "AddXMasterListCol", "Query for AddXMasterListCol", string.Format("Query:{0}", strInsert));
        //        Int32 res = sqlConn.SaveDataByFactory(strInsert);
        //        Util.WritInfoLog("$ConfigData$", "AddXMasterListCol", "End Query for AddXMasterListCol");


        //        ///////////////////////////
        //        strQry = " IF OBJECT_ID (N'" + ds.Tables[2].Rows[0]["TABLE_NAME"] + "', N'U') IS NOT NULL SELECT 1 AS res ";
        //        strQry = strQry + " ELSE SELECT 0 AS res";

        //        Util.WritInfoLog("$ConfigData$", "AddXMasterListCol", "Query for finding if table,_xref etc exists", string.Format("Query:{0}", strQry));
        //        Int16 intRes = Convert.ToInt16(sqlConn.ExecuteScalarByFactory(strQry));
        //        Util.WritInfoLog("$ConfigData$", "AddXMasterListCol", "End Query for finding if table,_xref etc exists");

        //        int res1 = 0, res2 = 0, res3 = 0, res4 = 0, res5 = 0;
        //        //rename table column name
        //        if (intRes == 1)
        //        {

        //            //add column in table_name
        //            res1 = Addcolumn(sqlConn, Convert.ToString(ds.Tables[2].Rows[0]["TABLE_NAME"]), false);
        //            //add column in TABLE_XREF
        //            res2 = Addcolumn(sqlConn, Convert.ToString(ds.Tables[2].Rows[0]["TABLE_XREF"]), false);
        //            //add column in TABLE_GREY
        //            res3 = Addcolumn(sqlConn, Convert.ToString(ds.Tables[2].Rows[0]["TABLE_GREY"]), false);
        //            //add column in TABLE_HISTORY
        //            res4 = Addcolumn(sqlConn, Convert.ToString(ds.Tables[2].Rows[0]["TABLE_HISTORY"]), true);
        //            //add column in TABLE_XREF_TEMP
        //            res5 = Addcolumn(sqlConn, Convert.ToString(ds.Tables[2].Rows[0]["TABLE_XREF_TEMP"]), false);
        //        }

        //        if (res == 1) return "true";
        //        else return "false";

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog("$ConfigData$", "AddXMasterListCol", "Error in AddXMasterListCol", "", ex);
        //        throw ex;
        //    }
        //}

        private Int32 Addcolumn(Connection Conn, string table_name, Boolean is_hist = false)
        {
            try
            {

                string strUpdate = string.Empty;
                //to change data type  -- keep it at one place
                strUpdate = "ALTER TABLE " + table_name;

                if (is_hist == false)
                {
                    strUpdate = strUpdate + " ADD " + allkeys["FIELD_NAME"] + " " + allkeys["DATA_TYPE"];

                    if (allkeys["DATA_TYPE"] == "varchar" || allkeys["DATA_TYPE"] == "nvarchar" || allkeys["DATA_TYPE"] == "char" || allkeys["DATA_TYPE"] == "float" || allkeys["DATA_TYPE"] == "decimal")
                    {
                        strUpdate = strUpdate + "(" + allkeys["PRECISION"] + ")";
                    }
                }
                else
                {
                    strUpdate = strUpdate + " ADD " + allkeys["FIELD_NAME"] + " " + " INT ";
                }

                Util.WritInfoLog("$ConfigData$", "AddXMasterListCol", "Query for add columns in " + table_name, string.Format("Query:{0}", strUpdate));
                Int32 res = Conn.SaveDataByFactory(strUpdate);
                Util.WritInfoLog("$ConfigData$", "AddXMasterListCol", "End Query for add columns in " + table_name);

                return res;
            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "Addcolumn", "Error in Addcolumn", "", ex);
                throw ex;
            }
        }


        public string DelXMasterListCol()
        {
            try
            {

                Connection sqlConn = new Connection();

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@ListColId", DbType.Int32, ParameterDirection.Input, 0, ListColId));

                Util.WritInfoLog("$ConfigData$", "DelXMasterListCol", "method for DelXMasterListCol", string.Format("Query:{0}", "usp_DeleteXMasterListCol"));
                Int32 res = Convert.ToInt32(sqlConn.SaveDataByFactoryByProc("usp_DeleteXMasterListCol", ref(lstparam)));
                Util.WritInfoLog("$ConfigData$", "DelXMasterListCol", "End method for DelXMasterListCol");

                return "true";
            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "DelXMasterListCol", "Error in DelXMasterListCol", "", ex);
                return "false";
                throw ex;
            }
        }


        //before SP
        //public string DelXMasterListCol()
        //{
        //    try
        //    {

        //        Connection sqlConn = new Connection();

        //        //get primary key column
        //        string strQry = " SELECT TABLE_NAME,TABLE_XREF,TABLE_GREY,TABLE_HISTORY FROM X_MASTER_LIST ";
        //        strQry = strQry + " WHERE LIST_ID =" + ListId;

        //        strQry = strQry + " SELECT * FROM X_MASTER_LIST_COLUMNS ";
        //        strQry = strQry + " WHERE LIST_COL_ID =" + ListColId;

        //        Util.WritInfoLog("$ConfigData$", "DelXMasterListCol", "Query for getting details in DelXMasterListCol", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog("$ConfigData$", "DelXMasterListCol", "End Query for getting details in DelXMasterListCol");

        //        DataTable dt = ds.Tables[0];
        //        DataTable dtCol = ds.Tables[1];

        //        //check validity
        //        //bool bdel = ValidateColDelete(ds.Tables[0]);
        //        bool bdel = true;

        //        if (bdel == true)
        //        {

        //            // string strDelQry = string.Empty;
        //            Int32 res;

        //            //strDelQry = "IF OBJECT_ID('" + dt.Rows[0]["TABLE_NAME"] + "') IS NOT NULL ";
        //            //strDelQry = strDelQry + "ALTER TABLE " + dt.Rows[0]["TABLE_NAME"];
        //            //strDelQry = strDelQry + " DROP COLUMN " + dtCol.Rows[0]["FIELD_NAME"];

        //            //res = sqlConn.SaveDataByFactory(strDelQry);

        //            //strDelQry = "IF OBJECT_ID('" + dt.Rows[0]["TABLE_XREF"] + "') IS NOT NULL ";
        //            //strDelQry = strDelQry + "ALTER TABLE " + dt.Rows[0]["TABLE_XREF"];
        //            //strDelQry = strDelQry + " DROP COLUMN " + dtCol.Rows[0]["FIELD_NAME"];

        //            //res = sqlConn.SaveDataByFactory(strDelQry);

        //            //strDelQry = "IF OBJECT_ID('" + dt.Rows[0]["TABLE_GREY"] + "') IS NOT NULL ";
        //            //strDelQry = strDelQry + "ALTER TABLE " + dt.Rows[0]["TABLE_GREY"];
        //            //strDelQry = strDelQry + " DROP COLUMN " + dtCol.Rows[0]["FIELD_NAME"];

        //            //res = sqlConn.SaveDataByFactory(strDelQry);

        //            //strDelQry = "IF OBJECT_ID('" + dt.Rows[0]["TABLE_HISTORY"] + "') IS NOT NULL ";
        //            //strDelQry = strDelQry + "ALTER TABLE " + dt.Rows[0]["TABLE_HISTORY"];
        //            //strDelQry = strDelQry + " DROP COLUMN " + dtCol.Rows[0]["FIELD_NAME"];

        //            //res = sqlConn.SaveDataByFactory(strDelQry);

        //            //check before delete in _rel table (get from from X_HIER_MASTER_LIST_REL)
        //            //strDelQry = " DELETE FROM X_MASTER_LIST_COLUMNS";
        //            //strDelQry = strDelQry + " WHERE LIST_COL_ID=" + ListColId;

        //            ////JavaScriptSerializer js = new JavaScriptSerializer();
        //            //res = sqlConn.SaveDataByFactory(strDelQry);

        //            //string strUpdQry = " UPDATE X_MASTER_LIST_COLUMNS ";
        //            //strUpdQry = strUpdQry + " SET IS_ACTIVE='False' ";
        //            //strUpdQry = strUpdQry + " WHERE LIST_COL_ID=" + ListColId;

        //            string strDelQry = " DELETE FROM X_MASTER_LIST_COLUMNS ";
        //            strDelQry = strDelQry + " WHERE LIST_COL_ID=" + ListColId;

        //            Util.WritInfoLog("$ConfigData$", "DelXMasterListCol", "Query for DelXMasterListCol", string.Format("Query:{0}", strDelQry));
        //            res = sqlConn.SaveDataByFactory(strDelQry);
        //            Util.WritInfoLog("$ConfigData$", "DelXMasterListCol", "End Query for DelXMasterListCol");

        //            return "true";
        //            // if (res == 1) return "true";
        //            //else return "false";              
        //        }
        //        else
        //        {
        //            return "Do You want to delete record data in use !:1";
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog("$ConfigData$", "DelXMasterListCol", "Error in DelXMasterListCol", "", ex);
        //        return "false";
        //        throw ex;
        //    }
        //}

        public List<object> SetDatarow(DataTable dt)
        {

            try
            {
                List<object> rows = new List<object>();

                foreach (DataRow dr in dt.Rows)
                {
                    Dictionary<string, object> row = new Dictionary<string, object>();
                    foreach (DataColumn dc in dt.Columns)
                    {
                        row.Add(dc.ColumnName, dr[dc].ToString());
                    }

                    rows.Add(row);
                }

                return rows;
            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "SetDatarow", "Error in SetDatarow", "", ex);
                return null;
                throw ex;
            }
        }

        public Grid SetColModel(Grid griddata, DataTable dt)
        {
            try
            {


                string primarykey = string.Empty;
                List<colModel> listColmodel = new List<colModel>();

                List<string> columns = new List<string>();

                int index = 0;
                //
                foreach (DataRow dr in dt.Rows)
                {

                    //for columns
                    columns.Add(dr["DISPLAY"].ToString());

                    //for colmodel
                    colModel colmodel = new colModel();
                    colmodel.index = dr["FIELD_NAME"].ToString();
                    colmodel.name = dr["FIELD_NAME"].ToString();
                    colmodel.width = 150;
                    colmodel.NullIfEmpty = true;

                    Dictionary<string, object> edoptions = new Dictionary<string, object>();
                    Dictionary<string, object> editrules = new Dictionary<string, object>();
                    Dictionary<string, object> formatoptions = new Dictionary<string, object>();

                    if (Convert.ToString(dr["PRECISION"]) != "")
                    {
                        edoptions.Add("maxlength", Convert.ToInt32(dr["PRECISION"]));
                    }

                    if (Convert.ToString(dr["IS_REQUIRED"]).ToLower() == "true" || Convert.ToInt32(dr["IS_REQUIRED"]) == 1)
                    {
                        editrules.Add("required", true);
                    }

                    if (dr["IS_PRIMARY"].ToString().ToLower() == "true" || dr["IS_PRIMARY"].ToString().ToLower() == "1")
                    {

                        primarykey = dr["FIELD_NAME"].ToString();
                        edoptions.Add("required", true);
                        edoptions.Add("edithidden", true);
                        colmodel.key = true;
                        colmodel.editable = false;
                    }
                    else
                    {
                        colmodel.editable = true;
                    }

                    if (dr["DATA_TYPE"].ToString().ToLower() == "number" || dr["DATA_TYPE"].ToString().ToLower() == "int" || dr["DATA_TYPE"].ToString().ToLower() == "real") //integer for whole number
                    {
                        colmodel.sorttype = "int";
                        colmodel.align = "right";

                        editrules.Add("number", true);
                        edoptions.Add("NullIfEmpty", true);
                        //formatter 
                        colmodel.formatter = "number";
                        //format option for number
                        formatoptions.Add("decimalSeparator", ".");
                        formatoptions.Add("defaulValue", 0);
                        formatoptions.Add("decimalPlaces", 2);
                    }
                    else if (dr["DATA_TYPE"].ToString().ToLower() == "varchar" || dr["DATA_TYPE"].ToString().ToLower() == "char" || dr["DATA_TYPE"].ToString().ToLower() == "nvarchar")
                    {
                        colmodel.sorttype = "string";
                        colmodel.align = "left";
                    }
                    else if (dr["DATA_TYPE"].ToString().ToLower() == "date")
                    {

                        colmodel.sorttype = "date";
                        colmodel.align = "right";
                        editrules.Add("date", true);
                        colmodel.formatter = "date";
                        formatoptions.Add("srcformat", "ISO8601Long");
                        formatoptions.Add("newformat", "m/d/Y");
                    }
                    else if (dr["DATA_TYPE"].ToString().ToLower() == "float")
                    {
                        colmodel.sorttype = "float";
                        colmodel.align = "right";

                        editrules.Add("number", true);
                        edoptions.Add("NullIfEmpty", true);
                        //formatter 
                        colmodel.formatter = "number";
                        //format option for number
                        formatoptions.Add("decimalSeparator", ".");
                        formatoptions.Add("defaulValue", 0);
                        formatoptions.Add("decimalPlaces", 2);
                    }


                    if (dr["DATA_TYPE"].ToString().ToLower() == "bit")
                    {
                        colmodel.edittype = "checkbox";//,editoptions:{value:"Yes:No"} 
                        edoptions.Add("value", "Yes:No");
                        colmodel.formatter = "checkbox";
                    }

                    if (dr["FIELD_NAME"].ToString() == "CONTROL_TYPE")
                    {
                        colmodel.edittype = "select";//editoptions:{value:"FE:FedEx;TN:TNT"}                     
                        string drpval = "textbox:textbox;checkbox:checkbox;textarea:textarea";
                        edoptions.Add("value", drpval); //values in this format
                        edoptions.Add("style", "width:160px");
                        // edoptions.Add("value", "0:Select;FE:FedEx;TN:TNT"); //values in this format
                        edoptions.Add("size", 400);
                    }
                    if (dr["FIELD_NAME"].ToString() == "DATA_TYPE")
                    {

                        colmodel.edittype = "select";//editoptions:{value:"FE:FedEx;TN:TNT"}
                        string drpval = "nvarchar:nvarchar;varchar:varchar;float:float;int:int;datetime:datetime;char:char;bigint:bigint";
                        //string drpval = "nnvarchar:nnvarchar,varchar:varchar,float:float,int:int,datetime:datetime,char:char,bigint:bigint";
                        edoptions.Add("value", drpval); //values in this format
                        edoptions.Add("style", "width:160px");
                        edoptions.Add("size", 7);
                        //add event to dropdown of datatype

                        //Dictionary<string, object> dataEvents = new Dictionary<string, object>();
                        //dataEvents.Add("type", "change");
                        //object strFun="{ function (e) { alert('hi');} }";
                        //dataEvents.Add("fn", strFun);
                        //edoptions.Add("dataEvents", dataEvents);
                    }


                    if (dr["FIELD_NAME"].ToString() == "PASSWORD")
                    {
                        colmodel.edittype = "password";
                        colmodel.hidden = true;
                        editrules.Add("edithidden", true);
                        //colmodel.e
                    }

                    //exception
                    if (dr["DISPLAY"].ToString().ToUpper() == "TABLE_NAME" || dr["DISPLAY"].ToString() == "IsTableCreated" || dr["DISPLAY"].ToString() == "hasUser")//hasUser
                    {
                        colmodel.hidden = true;
                    }


                    ////control type
                    //if (dr["CONTROL_TYPE"].ToString().ToLower() == "textarea")
                    //{
                    //    colmodel.edittype = dr["CONTROL_TYPE"].ToString().ToLower();
                    //    edoptions.Add("rows", "2");
                    //    edoptions.Add("cols", "20");
                    //}
                    //else if (dr["CONTROL_TYPE"].ToString().ToLower() == "checkbox")
                    //{
                    //    colmodel.edittype = "checkbox";//,editoptions:{value:"Yes:No"} 
                    //    edoptions.Add("value", "Yes:No");
                    //    colmodel.formatter = "checkbox";
                    //}

                    //else if (dr["CONTROL_TYPE"].ToString().ToLower() == "dropdown")
                    //{
                    //    colmodel.edittype = "select";//editoptions:{value:"FE:FedEx;TN:TNT"}
                    //    //colmodel.formatter = "select";
                    //    string drpval = formDropdownString(dr["DROPDOWN_VAL"].ToString());
                    //    edoptions.Add("value", drpval); //values in this format
                    //    edoptions.Add("style", "width:160px");
                    //    // edoptions.Add("value", "0:Select;FE:FedEx;TN:TNT"); //values in this format
                    //    //edoptions.Add("size", 400);
                    //}

                    colmodel.editoptions = edoptions;
                    colmodel.editrules = editrules;
                    colmodel.formatoptions = formatoptions;

                    listColmodel.Add(colmodel);
                    index++;
                }

                griddata.colModelList = listColmodel;
                griddata.columns = columns;
                griddata.primarykey = primarykey;

                return griddata;
            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "SetColModel", "Error in SetColModel", "", ex);
                return null;
                throw ex;
            }
        }

        public bool ValidateXMListDelete(string strTableName, string strprimarykey)
        {

            try
            {

                Connection sqlConn = new Connection();
                string strQry = " SELECT COUNT(*) FROM " + strTableName;
                strQry = strQry + " WHERE " + strprimarykey + "= " + id;

                Util.WritInfoLog("$ConfigData$", "ValidateXMListDelete", "Query for ValidateXMListDelete", string.Format("Query:{0}", strQry));
                Int32 intCnt = Convert.ToInt32(sqlConn.ExecuteScalarByFactory(strQry));
                Util.WritInfoLog("$ConfigData$", "ValidateXMListDelete", "End Query for ValidateXMListDelete");

                if (intCnt == 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "ValidateXMListDelete", "Error in ValidateXMListDelete", "", ex);
                throw ex;
            }
        }

        public bool ValidateColDelete(DataTable dt)
        {

            try
            {

                Connection sqlConn = new Connection();
                Boolean bflag = true;

                string strQry = "SELECT COUNT(*) FROM sys.objects where name=('" + dt.Rows[0]["TABLE_NAME"] + "')";
                strQry = strQry + "SELECT COUNT(*) FROM sys.objects where name=('" + dt.Rows[0]["TABLE_XREF"] + "')";
                strQry = strQry + "SELECT COUNT(*) FROM sys.objects where name=('" + dt.Rows[0]["TABLE_GREY"] + "')";
                strQry = strQry + "SELECT COUNT(*) FROM sys.objects where name=('" + dt.Rows[0]["TABLE_HISTORY"] + "')";

                //strQry = strQry + " SELECT COUNT(*) FROM " + dt.Rows[0]["TABLE_NAME"];
                //strQry = strQry + " SELECT COUNT(*) FROM " + dt.Rows[0]["TABLE_XREF"];
                //strQry = strQry + " SELECT COUNT(*) FROM " + dt.Rows[0]["TABLE_GREY"];
                //strQry = strQry + " SELECT COUNT(*) FROM " + dt.Rows[0]["TABLE_HISTORY"];

                Util.WritInfoLog("$ConfigData$", "ValidateColDelete", "Query for ValidateColDelete", string.Format("Query:{0}", strQry));
                DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
                Util.WritInfoLog("$ConfigData$", "ValidateColDelete", "End Query for ValidateColDelete");

                //DataTable dtVal=ds.Tables[0];

                //if object exists
                if (Convert.ToInt16(ds.Tables[0].Rows[0][0]) == 1 || Convert.ToInt16(ds.Tables[1].Rows[0][0]) == 1 || Convert.ToInt16(ds.Tables[2].Rows[0][0]) == 1 || Convert.ToInt16(ds.Tables[1].Rows[0][0]) == 1)
                {
                    // if (Convert.ToInt16(dtVal.Rows[4][0]) == 1 || Convert.ToInt16(dtVal.Rows[5][0]) == 1 || Convert.ToInt16(dtVal.Rows[6][0]) == 1 || Convert.ToInt16(dtVal.Rows[7][0]) == 1)
                    //{
                    bflag = false;
                    //}
                }

                return bflag;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "ValidateColDelete", "Error in ValidateColDelete", "", ex);
                throw ex;
            }
        }

        public string EditXMasterListCol()
        {
            try
            {

                Connection sqlConn = new Connection();
                List<dict> allkey = new List<dict>();

                foreach (string key in allkeys)
                {
                    dict d = new dict();
                    d.key1 = key;
                    d.value = allkeys[key];
                    allkey.Add(d);
                }

                var xmlDocument = new XmlDocument();
                var serializer = new XmlSerializer(typeof(List<dict>));

                using (var stream = new MemoryStream())
                {
                    serializer.Serialize(stream, allkey);
                    stream.Flush(); stream.Seek(0, SeekOrigin.Begin);
                    xmlDocument.Load(stream);
                }

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@field_Xml", DbType.Xml, ParameterDirection.Input, 0, xmlDocument.OuterXml));
                lstparam.Add(new DBParameter("@ListId", DbType.Int32, ParameterDirection.Input, 0, ListId));
                lstparam.Add(new DBParameter("@ListColId", DbType.Int32, ParameterDirection.Input, 0, ListColId));

                Util.WritInfoLog(ClsName, "EditXMasterListCol", "method for EditXMasterListCol", "usp_editXMasterListCol");
                Int32 res = Convert.ToInt32(sqlConn.SaveDataByFactoryByProc("usp_editXMasterListCol", ref(lstparam)));
                Util.WritInfoLog(ClsName, "EditXMasterListCol", "End method for EditXMasterListCol");

                return "true";
            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "EditXMasterListCol", "Error in EditXMasterListCol", "", ex);
                return "false";
                throw ex;
            }
        }


        //before SP
        //public string EditXMasterListCol()
        //{
        //    try
        //    {

        //        Connection sqlConn = new Connection();

        //        string strQry = "  SELECT sys.types.name DATA_TYPE,sys.columns.name as FIELD_NAME ,";
        //        strQry = strQry + "ISNULL(i.is_primary_key, 0) IS_PRIMARY ";
        //        strQry = strQry + " FROM sys.columns  ";
        //        strQry = strQry + " INNER JOIN sys.types ";
        //        strQry = strQry + " ON sys.columns.system_type_id =sys.types.system_type_id	";
        //        strQry = strQry + "LEFT OUTER JOIN    sys.index_columns ic ON ic.object_id = sys.columns.object_id ";
        //        strQry = strQry + " AND ic.column_id = sys.columns.column_id ";
        //        strQry = strQry + "LEFT OUTER JOIN     sys.indexes i ON ic.object_id = i.object_id ";
        //        strQry = strQry + "AND ic.index_id = i.index_id ";
        //        strQry = strQry + "WHERE sys.columns.object_id = OBJECT_ID('X_MASTER_LIST_COLUMNS')";

        //        strQry = strQry + " SELECT * FROM X_MASTER_LIST_COLUMNS";
        //        strQry = strQry + " WHERE LIST_COL_ID=" + ListColId;

        //        strQry = strQry + " SELECT * FROM X_MASTER_LIST";
        //        strQry = strQry + " WHERE LIST_ID=" + ListId;

        //        Util.WritInfoLog("$ConfigData$", "EditXMasterListCol", "Query for getting details in EditXMasterListCol", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog("$ConfigData$", "EditXMasterListCol", "End Query for getting details in EditXMasterListCol");

        //        DataTable dt = ds.Tables[0];
        //        DataTable dtCol = ds.Tables[1];

        //        DataView Dv = new DataView(dt);
        //        Dv.RowFilter = " IS_PRIMARY = FALSE AND FIELD_NAME <> 'LIST_ID'";
        //        string strUpdate = "UPDATE X_MASTER_LIST_COLUMNS SET ";

        //        string colNm = string.Empty;
        //        string colVl = string.Empty;
        //        string strUpd = string.Empty;

        //        foreach (DataRowView dr in Dv)
        //        {

        //            if (dr["DATA_TYPE"].ToString().ToLower() == "varchar" || dr["DATA_TYPE"].ToString().ToLower() == "char" || dr["DATA_TYPE"].ToString().ToLower() == "nvarchar")
        //            {
        //                strUpdate = strUpdate + dr["FIELD_NAME"].ToString() + "='" + allkeys[dr["FIELD_NAME"].ToString()] + "'" + ",";
        //            }
        //            else if (dr["DATA_TYPE"].ToString().ToLower() == "date")
        //            {
        //                strUpdate = strUpdate + dr["FIELD_NAME"].ToString() + "='" + Convert.ToDateTime(allkeys[dr["FIELD_NAME"].ToString()]).ToString("MM/dd/yyyy") + "'" + ",";
        //            }
        //            else if (dr["DATA_TYPE"].ToString().ToLower() == "number" || dr["DATA_TYPE"].ToString().ToLower() == "float")
        //            {
        //                strUpdate = strUpdate + dr["FIELD_NAME"].ToString() + "=" + allkeys[dr["FIELD_NAME"].ToString()] + ",";
        //            }
        //            else if (dr["DATA_TYPE"].ToString().ToLower() == "bit")
        //            {
        //                if (allkeys[dr["FIELD_NAME"].ToString()] == "Yes")
        //                {
        //                    strUpdate = strUpdate + dr["FIELD_NAME"].ToString() + "=1,";
        //                }
        //                else
        //                {
        //                    strUpdate = strUpdate + dr["FIELD_NAME"].ToString() + "=0,";
        //                }
        //            }
        //        }

        //        strUpdate = strUpdate.Substring(0, strUpdate.Length - 1);
        //        //where condition
        //        strUpdate = strUpdate + " WHERE LIST_COL_ID=" + ListColId;

        //        Util.WritInfoLog("$ConfigData$", "EditXMasterListCol", "Query for update in X_MASTER_LIST_COLUMNS", string.Format("Query:{0}", strUpdate));
        //        Int32 res = sqlConn.SaveDataByFactory(strUpdate);
        //        Util.WritInfoLog("$ConfigData$", "EditXMasterListCol", "End Query for update in X_MASTER_LIST_COLUMNS");

        //        ///////////////////////////
        //        strQry = "SELECT COUNT(*) AS CNT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + ds.Tables[2].Rows[0]["TABLE_NAME"] + "' AND ";
        //        strQry = strQry + " COLUMN_NAME = '" + dtCol.Rows[0]["FIELD_NAME"] + "'";

        //        strQry = strQry + " SELECT COUNT(*) AS CNT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + ds.Tables[2].Rows[0]["TABLE_XREF"] + "' AND ";
        //        strQry = strQry + " COLUMN_NAME = '" + dtCol.Rows[0]["FIELD_NAME"] + "'";

        //        strQry = strQry + " SELECT COUNT(*) AS CNT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + ds.Tables[2].Rows[0]["TABLE_GREY"] + "' AND ";
        //        strQry = strQry + " COLUMN_NAME = '" + dtCol.Rows[0]["FIELD_NAME"] + "'";

        //        strQry = strQry + " SELECT COUNT(*) AS CNT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + ds.Tables[2].Rows[0]["TABLE_HISTORY"] + "' AND ";
        //        strQry = strQry + " COLUMN_NAME = '" + dtCol.Rows[0]["FIELD_NAME"] + "'";

        //        strQry = strQry + " SELECT COUNT(*) AS CNT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + ds.Tables[2].Rows[0]["TABLE_XREF_TEMP"] + "' AND ";
        //        strQry = strQry + " COLUMN_NAME = '" + dtCol.Rows[0]["FIELD_NAME"] + "'";

        //        Util.WritInfoLog("$ConfigData$", "EditXMasterListCol", "Query for finding if table,_xref etc exists", string.Format("Query:{0}", strQry));
        //        DataSet dsEx = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog("$ConfigData$", "EditXMasterListCol", "End Query for finding if table,_xref etc exists");

        //        //DataTable dtexist=dsExist.Tables[0]

        //        //rename table column name
        //        if (Convert.ToInt32(dsEx.Tables[0].Rows[0]["CNT"]) > 0)
        //        {

        //            ////strUpdate = "sp_RENAME '" + dt.Rows[0]["TABLE_NAME"] + "' ," + " 'M_" + allkeys["TABLE_ABBR"] + "'";
        //            //strUpdate = "sp_RENAME '" + ds.Tables[2].Rows[0]["TABLE_NAME"] + "." + dtCol.Rows[0]["FIELD_NAME"] + "','" + allkeys["FIELD_NAME"] + "'";
        //            ////sp_rename 'a.x', 'y', 'COLUMN';                  
        //            //res = sqlConn.SaveDataByFactory(strUpdate);

        //            //if (allkeys["DATA_TYPE"] == "varchar" || allkeys["DATA_TYPE"] == "nvarchar" || allkeys["DATA_TYPE"] == "char" || allkeys["DATA_TYPE"] == "float" || allkeys["DATA_TYPE"] == "decimal")
        //            // {

        //            //to change data type  -- keep it at one place
        //            strUpdate = "ALTER TABLE " + ds.Tables[2].Rows[0]["TABLE_NAME"];
        //            strUpdate = strUpdate + " ALTER COLUMN " + allkeys["FIELD_NAME"] + " " + allkeys["DATA_TYPE"];
        //            if (allkeys["DATA_TYPE"] == "varchar" || allkeys["DATA_TYPE"] == "nvarchar" || allkeys["DATA_TYPE"] == "char" || allkeys["DATA_TYPE"] == "float" || allkeys["DATA_TYPE"] == "decimal")
        //            {
        //                strUpdate = strUpdate + "(" + allkeys["PRECISION"] + ")";
        //            }

        //            //if (allkeys["IS_REQUIRED"].ToString() == "Yes")
        //            //{
        //            //    strUpdate = strUpdate + " NOT NULL ";
        //            //}

        //            Util.WritInfoLog("$ConfigData$", "EditXMasterListCol", "Query for update columns in table_name", string.Format("Query:{0}", strUpdate));
        //            res = sqlConn.SaveDataByFactory(strUpdate);
        //            Util.WritInfoLog("$ConfigData$", "EditXMasterListCol", "End Query for update columns in table_name");

        //            //}

        //            ////if field is not null then only it will create that
        //            //if (allkeys["IS_REQUIRED"].ToString() == "Yes")
        //            //{
        //            //    if (allkeys["IS_PRIMARY"].ToString() == "Yes")
        //            //    {
        //            //        strUpdate = " ALTER TABLE " + ds.Tables[2].Rows[0]["TABLE_NAME"];
        //            //        strUpdate = strUpdate + " ADD PRIMARY KEY ( " + allkeys["FIELD_NAME"] + ")";
        //            //        res = sqlConn.SaveDataByFactory(strUpdate);
        //            //    }
        //            //}

        //            // ALTER TABLE dbo.YourTable   
        //            // ALTER COLUMN YourColumnName BIT 
        //        }

        //        //strUpdate = "IF OBJECT_ID('" + dt.Rows[0]["TABLE_XREF"] + "') IS NOT NULL ";
        //        if (Convert.ToInt32(dsEx.Tables[1].Rows[0]["CNT"]) > 0)
        //        {
        //            ////strUpdate = "sp_RENAME '" + dt.Rows[0]["TABLE_XREF"] + "' ," + " 'M_" + allkeys["TABLE_ABBR"] + "_XREF'";
        //            //strUpdate = "sp_RENAME '" + ds.Tables[2].Rows[0]["TABLE_XREF"] + "." + dtCol.Rows[0]["FIELD_NAME"] + "','" + allkeys["FIELD_NAME"] + "'";
        //            //res = sqlConn.SaveDataByFactory(strUpdate);

        //            //if (allkeys["DATA_TYPE"] == "varchar" || allkeys["DATA_TYPE"] == "nvarchar" || allkeys["DATA_TYPE"] == "char" || allkeys["DATA_TYPE"] == "float" || allkeys["DATA_TYPE"] == "decimal")
        //            //{

        //            strUpdate = "ALTER TABLE " + ds.Tables[2].Rows[0]["TABLE_XREF"];
        //            strUpdate = strUpdate + " ALTER COLUMN " + allkeys["FIELD_NAME"] + " " + allkeys["DATA_TYPE"];
        //            if (allkeys["DATA_TYPE"] == "varchar" || allkeys["DATA_TYPE"] == "nvarchar" || allkeys["DATA_TYPE"] == "char" || allkeys["DATA_TYPE"] == "float" || allkeys["DATA_TYPE"] == "decimal")
        //            {
        //                strUpdate = strUpdate + "(" + allkeys["PRECISION"] + ")";
        //            }

        //            //if (allkeys["IS_REQUIRED"].ToString() == "Yes")
        //            //{
        //            //    strUpdate = strUpdate + " NOT NULL ";
        //            //}

        //            Util.WritInfoLog("$ConfigData$", "EditXMasterListCol", "Query for update columns in table_xref", string.Format("Query:{0}", strUpdate));
        //            res = sqlConn.SaveDataByFactory(strUpdate);
        //            Util.WritInfoLog("$ConfigData$", "EditXMasterListCol", "End Query for update columns in table_xref");


        //            //}

        //        }

        //        //for xref_temp
        //        if (Convert.ToInt32(dsEx.Tables[2].Rows[0]["CNT"]) > 0)
        //        {
        //            ////strUpdate = "IF OBJECT_ID('" + dt.Rows[0]["TABLE_GREY"] + "') IS NOT NULL ";
        //            ////strUpdate = "sp_RENAME '" + dt.Rows[0]["TABLE_GREY"] + "' ," + " 'M_" + allkeys["TABLE_ABBR"] + "_GREY'";
        //            //strUpdate = "sp_RENAME '" + ds.Tables[2].Rows[0]["TABLE_GREY"] + "." + dtCol.Rows[0]["FIELD_NAME"] + "','" + allkeys["FIELD_NAME"] + "'";
        //            //res = sqlConn.SaveDataByFactory(strUpdate);

        //            // if (allkeys["DATA_TYPE"] == "varchar" || allkeys["DATA_TYPE"] == "nvarchar" || allkeys["DATA_TYPE"] == "char" || allkeys["DATA_TYPE"] == "float" || allkeys["DATA_TYPE"] == "decimal")
        //            // {

        //            strUpdate = "ALTER TABLE " + ds.Tables[2].Rows[0]["TABLE_GREY"];
        //            strUpdate = strUpdate + " ALTER COLUMN " + allkeys["FIELD_NAME"] + " " + allkeys["DATA_TYPE"];
        //            if (allkeys["DATA_TYPE"] == "varchar" || allkeys["DATA_TYPE"] == "nvarchar" || allkeys["DATA_TYPE"] == "char" || allkeys["DATA_TYPE"] == "float" || allkeys["DATA_TYPE"] == "decimal")
        //            {
        //                strUpdate = strUpdate + "(" + allkeys["PRECISION"] + ")";
        //            }

        //            //if (allkeys["IS_REQUIRED"].ToString() == "Yes")
        //            //{
        //            //    strUpdate = strUpdate + " NOT NULL ";
        //            //}
        //            // }

        //            Util.WritInfoLog("$ConfigData$", "EditXMasterListCol", "Query for update columns in table_grey", string.Format("Query:{0}", strUpdate));
        //            res = sqlConn.SaveDataByFactory(strUpdate);
        //            Util.WritInfoLog("$ConfigData$", "EditXMasterListCol", "End Query for update columns in table_grey");


        //        }

        //        //if (Convert.ToInt32(dsEx.Tables[3].Rows[0]["CNT"]) > 0)
        //        //{
        //        //    //strUpdate = "IF OBJECT_ID('" + dt.Rows[0]["TABLE_HISTORY"] + "') IS NOT NULL ";
        //        //    //strUpdate = "sp_RENAME '" + dt.Rows[0]["TABLE_HISTORY"] + "' ," + " 'M_" + allkeys["TABLE_HISTORY"] + "_HISTORY'";
        //        //    strUpdate = "sp_RENAME '" + ds.Tables[2].Rows[0]["TABLE_HISTORY"] + "." + dtCol.Rows[0]["FIELD_NAME"] + "','" + allkeys["FIELD_NAME"] + "'";
        //        //    res = sqlConn.SaveDataByFactory(strUpdate);

        //        //    strUpdate = " ALTER TABLE " + ds.Tables[2].Rows[0]["TABLE_HISTORY"];
        //        //    strUpdate = strUpdate + " ALTER COLUMN " + allkeys["FIELD_NAME"] + " " + allkeys["DATA_TYPE"];
        //        //    if (allkeys["DATA_TYPE"] == "varchar" || allkeys["DATA_TYPE"] == "nvarchar" || allkeys["DATA_TYPE"] == "char" || allkeys["DATA_TYPE"] == "float" || allkeys["DATA_TYPE"] == "decimal")
        //        //    {
        //        //        strUpdate = strUpdate + "(" + allkeys["PRECISION"] + ")";
        //        //    }

        //        //    if (allkeys["IS_REQUIRED"].ToString() == "Yes")
        //        //    {
        //        //        strUpdate = strUpdate + " NOT NULL ";
        //        //    }

        //        //    res = sqlConn.SaveDataByFactory(strUpdate);

        //        //}

        //        if (Convert.ToInt32(dsEx.Tables[4].Rows[0]["CNT"]) > 0)
        //        {

        //            // if (allkeys["DATA_TYPE"] == "varchar" || allkeys["DATA_TYPE"] == "nvarchar" || allkeys["DATA_TYPE"] == "char" || allkeys["DATA_TYPE"] == "float" || allkeys["DATA_TYPE"] == "decimal")
        //            // {

        //            strUpdate = "ALTER TABLE " + ds.Tables[2].Rows[0]["TABLE_XREF_TEMP"];
        //            strUpdate = strUpdate + " ALTER COLUMN " + allkeys["FIELD_NAME"] + " " + allkeys["DATA_TYPE"];
        //            if (allkeys["DATA_TYPE"] == "varchar" || allkeys["DATA_TYPE"] == "nvarchar" || allkeys["DATA_TYPE"] == "char" || allkeys["DATA_TYPE"] == "float" || allkeys["DATA_TYPE"] == "decimal")
        //            {
        //                strUpdate = strUpdate + "(" + allkeys["PRECISION"] + ")";
        //            }

        //            //if (allkeys["IS_REQUIRED"].ToString() == "Yes")
        //            //{
        //            //    strUpdate = strUpdate + " NOT NULL ";
        //            //}

        //            Util.WritInfoLog("$ConfigData$", "EditXMasterListCol", "Query for update columns in table_xref_temp", string.Format("Query:{0}", strUpdate));
        //            res = sqlConn.SaveDataByFactory(strUpdate);
        //            Util.WritInfoLog("$ConfigData$", "EditXMasterListCol", "End Query for update columns in table_xref_temp");
        //            // }
        //        }

        //        return "true";

        //        //if (res == 1) return "true";
        //        //else return "false";
        //        // return res.ToString();
        //        //return js.Serialize(new Records(sqlConn.ExecuteCommand(strUpdate)));

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog("$ConfigData$", "EditXMasterListCol", "Error in EditXMasterListCol", "", ex);
        //        return "false";
        //        throw ex;
        //    }
        //}


        public string CreateDimConfigTable()
        {
            try
            {

                Connection sqlConn = new Connection();

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@ListId", DbType.Int32, ParameterDirection.Input, 0, ListId));

                Util.WritInfoLog(ClsName, "CreateDimConfigTable", "method for getting XMasterList and _Columns data", "usp_GetXMasterList&ColumnsData");
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetXMasterList&ColumnsData", ref(lstparam));
                Util.WritInfoLog(ClsName, "CreateDimConfigTable", "End method for getting XMasterList and _Columns data");                
                
                DataTable dt = ds.Tables[0];
                Int16 res;
                DataView dv = new DataView(dt);
                dv.RowFilter = "IS_PRIMARY =false";

                string strFields = GenerateCreateTableString(dv.ToTable(), false);
                string strHist = GenerateCreateTableHist(dv.ToTable());

                List<DBParameter> lstparam1 = new List<DBParameter>();
                lstparam1.Add(new DBParameter("@ListId", DbType.Int32, ParameterDirection.Input, 0, ListId));
                lstparam1.Add(new DBParameter("@Fields", DbType.String, ParameterDirection.Input,4000, strFields));
                lstparam1.Add(new DBParameter("@FieldsHist", DbType.String, ParameterDirection.Input, 4000, strHist));
                lstparam.Add(new DBParameter("@returnValue", DbType.Int16, ParameterDirection.ReturnValue));

                Util.WritInfoLog(ClsName, "CreateDimConfigTable", "method for CreateDimConfigTable", "usp_CreateDimConfigTable");
                res = Convert.ToInt16(sqlConn.GetDataSetFromFactoryProc("usp_CreateDimConfigTable", ref(lstparam1)));
                Util.WritInfoLog(ClsName, "CreateDimConfigTable", "End method for CreateDimConfigTable");

                int retVal = 0;

                foreach (DBParameter dbparam in lstparam)
                {
                    if (dbparam.name == "@returnValue")
                    {
                        retVal = Convert.ToInt32(dbparam.value);
                        break;
                    }
                }

                if (retVal==1)
                   return "true";
                else 
                   return "false";

            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "CreateDimConfigTable", "Error in CreateDimConfigTable", "", ex);
                return "false";
                throw ex;
            }
        }

        //before SP
        //CreateDimConfigTable
        //public string CreateDimConfigTable()
        //{
        //    try
        //    {

        //        Connection sqlConn = new Connection();

        //        string strQry = " SELECT X_MASTER_LIST.*,X_MASTER_LIST_COLUMNS.* FROM X_MASTER_LIST";
        //        strQry = strQry + " INNER JOIN X_MASTER_LIST_COLUMNS ";
        //        strQry = strQry + " ON X_MASTER_LIST.LIST_ID=X_MASTER_LIST_COLUMNS.LIST_ID ";
        //        strQry = strQry + " WHERE X_MASTER_LIST.LIST_ID=" + ListId;

        //        //strQry = strQry + " SELECT MAX(LIST_COL_ID)+ 1 AS LIST_COL_ID FROM X_MASTER_LIST_COLUMNS";

        //        Util.WritInfoLog("$ConfigData$", "CreateDimConfigTable", "Query for details in CreateDimConfigTable", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog("$ConfigData$", "CreateDimConfigTable", "End Query for details in CreateDimConfigTable");

        //        DataTable dt = ds.Tables[0];
        //        Int32 res;

        //        //insert dim_id in x_master_list_columns
        //        //int intCol_ID = Convert.ToInt32(ds.Tables[1].Rows[0][0]);

        //        string strInsertDim = " INSERT INTO X_MASTER_LIST_COLUMNS ";
        //        strInsertDim = strInsertDim + " (DISPLAY,FIELD_NAME,DATA_TYPE,PRECISION, ";
        //        strInsertDim = strInsertDim + " IS_REQUIRED,DROPDOWN_VAL,CONTROL_TYPE,IS_PRIMARY,IS_ACTIVE) ";
        //        strInsertDim = strInsertDim + "VALUES (" ;
        //        strInsertDim = strInsertDim + "'DIM_ID','DIM_ID','INT',0,'TRUE',NULL,'textbox','True','True')";

        //        Util.WritInfoLog("$ConfigData$", "CreateDimConfigTable", "Query for insert into x_master_list_col DIM_ID", string.Format("Query:{0}", strInsertDim));
        //        res = sqlConn.SaveDataByFactory(strInsertDim);
        //        Util.WritInfoLog("$ConfigData$", "CreateDimConfigTable", "End Query for insert into x_master_list_col DIM_ID");

        //        DataView dv = new DataView(dt);
        //        dv.RowFilter = "IS_PRIMARY =false";

        //        //create table_name
        //        string strCreate = "CREATE TABLE " + dt.Rows[0]["TABLE_NAME"] + "(";
        //        strCreate = strCreate + "DIM_ID INT IDENTITY(1,1) NOT NULL,";
        //        strCreate = strCreate + GenerateCreateTableString(dv.ToTable(), false);
        //        strCreate = strCreate + ")";
        //        strCreate = strCreate + "ALTER TABLE " + dt.Rows[0]["TABLE_NAME"] + " ADD CONSTRAINT [PK_";
        //        strCreate = strCreate + dt.Rows[0]["TABLE_NAME"].ToString().Trim() + "] PRIMARY KEY CLUSTERED ([DIM_ID] ASC )";

        //        Util.WritInfoLog("$ConfigData$", "CreateDimConfigTable", "Query for create table", string.Format("Query:{0}", strCreate));
        //        res = sqlConn.SaveDataByFactory(strCreate);
        //        Util.WritInfoLog("$ConfigData$", "CreateDimConfigTable", "End Query for create table");

        //        //create table_xref
        //        strCreate = "CREATE TABLE " + dt.Rows[0]["TABLE_XREF"] + "(";
        //        strCreate = strCreate + "XREF_ID INT IDENTITY(1,1) NOT NULL,";
        //        // strCreate = strCreate + "DIM_ID INT ,";
        //        strCreate = strCreate + GenerateCreateTableString(dv.ToTable(), false);
        //        strCreate = strCreate + ",SOURCE varchar(255) NOT NULL";
        //        strCreate = strCreate + ",SOURCE_ID INT NOT NULL,";
        //        strCreate = strCreate + "MASTER_ID INT NOT NULL,";
        //        strCreate = strCreate + "MANUAL_RULE_ID INT NULL";//MANUAL_RULE_ID
        //        strCreate = strCreate + ")";
        //        strCreate = strCreate + "ALTER TABLE " + dt.Rows[0]["TABLE_XREF"] + " ADD CONSTRAINT [PK_";
        //        strCreate = strCreate + dt.Rows[0]["TABLE_XREF"].ToString().Trim() + "] PRIMARY KEY CLUSTERED ([XREF_ID] ASC )";


        //        Util.WritInfoLog("$ConfigData$", "CreateDimConfigTable", "Query for create table_xref", string.Format("Query:{0}", strCreate));
        //        res = sqlConn.SaveDataByFactory(strCreate);
        //        Util.WritInfoLog("$ConfigData$", "CreateDimConfigTable", "End Query for create table_xref");


        //        //create table_xref_temp              
        //        strCreate = "CREATE TABLE " + dt.Rows[0]["TABLE_XREF_TEMP"] + "(";
        //        strCreate = strCreate + "XREF_ID INT IDENTITY (1,1) NOT NULL,";
        //        strCreate = strCreate + GenerateCreateTableString(dv.ToTable(), false);
        //        strCreate = strCreate + ",SOURCE varchar(255) NULL";
        //        strCreate = strCreate + ",SOURCE_ID INT NULL,";
        //        strCreate = strCreate + "MASTER_ID INT NULL,";
        //        strCreate = strCreate + "MANUAL_RULE_ID INT NULL,";
        //        strCreate = strCreate + "IMP_ID INT NULL";
        //        strCreate = strCreate + ")";
        //        strCreate = strCreate + "ALTER TABLE " + dt.Rows[0]["TABLE_XREF_TEMP"] + " ADD CONSTRAINT [PK_";
        //        strCreate = strCreate + dt.Rows[0]["TABLE_XREF_TEMP"].ToString().Trim() + "] PRIMARY KEY CLUSTERED ([XREF_ID] ASC )";

        //        Util.WritInfoLog("$ConfigData$", "CreateDimConfigTable", "Query for creating Xref_temp", string.Format("Query:{0}", strCreate));
        //        Int32 ret2 = sqlConn.SaveDataByFactory(strCreate);
        //        Util.WritInfoLog("$ConfigData$", "CreateDimConfigTable", "End Query for creating Xref_temp");

        //        //create table_xref_TEMPDETAIL              
        //        strCreate = "CREATE TABLE " + dt.Rows[0]["TABLE_XREF_TEMPDETAIL"] + "(";
        //        strCreate = strCreate + "XREF_TEMP_DETID INT IDENTITY (1,1) NOT NULL,";
        //        strCreate = strCreate + "RULE_ID INT NULL,";
        //        strCreate = strCreate + "XREF_ID INT NULL,";
        //        strCreate = strCreate + "MASTER_ID INT NULL,";
        //        strCreate = strCreate + "IMP_ID INT NULL";
        //        strCreate = strCreate + ")";
        //        strCreate = strCreate + "ALTER TABLE " + dt.Rows[0]["TABLE_XREF_TEMPDETAIL"] + " ADD CONSTRAINT [PK_";
        //        strCreate = strCreate + dt.Rows[0]["TABLE_XREF_TEMPDETAIL"].ToString().Trim() + "] PRIMARY KEY CLUSTERED ([XREF_TEMP_DETID] ASC )";

        //        Util.WritInfoLog("$ConfigData$", "CreateDimConfigTable", "Query for table_xref_TEMPDETAIL", string.Format("Query:{0}", strCreate));
        //        ret2 = sqlConn.SaveDataByFactory(strCreate);
        //        Util.WritInfoLog("$ConfigData$", "CreateDimConfigTable", "End Query for table_xref_TEMPDETAIL");

        //        //create table_grey
        //        strCreate = "CREATE TABLE " + dt.Rows[0]["TABLE_GREY"] + "(";
        //        strCreate = strCreate + "GREY_ID INT IDENTITY (1,1) NOT NULL,";
        //        // strCreate = strCreate + "DIM_ID INT ,";
        //        strCreate = strCreate + GenerateCreateTableString(dv.ToTable(), false);
        //        strCreate = strCreate + ",SOURCE varchar(255) NOT NULL";
        //        strCreate = strCreate + ",SOURCE_ID INT NOT NULL,";
        //        strCreate = strCreate + "MASTER_ID INT NOT NULL";
        //        strCreate = strCreate + ")";
        //        strCreate = strCreate + "ALTER TABLE " + dt.Rows[0]["TABLE_GREY"] + " ADD CONSTRAINT [PK_";
        //        strCreate = strCreate + dt.Rows[0]["TABLE_GREY"].ToString().Trim() + "] PRIMARY KEY CLUSTERED ([GREY_ID] ASC )";

        //        Util.WritInfoLog("$ConfigData$", "CreateDimConfigTable", "Query for create table_grey", string.Format("Query:{0}", strCreate));
        //        res = sqlConn.SaveDataByFactory(strCreate);
        //        Util.WritInfoLog("$ConfigData$", "CreateDimConfigTable", "End Query for create table_grey");

        //        //create table_history
        //        strCreate = "CREATE TABLE " + dt.Rows[0]["TABLE_HISTORY"] + "(";
        //        strCreate = strCreate + "HIST_ID INT IDENTITY (1,1) NOT NULL,";
        //        // strCreate = strCreate + "DIM_ID INT ,";
        //        strCreate = strCreate + GenerateCreateTableHist(dv.ToTable());
        //        strCreate = strCreate + ",MASTER_ID INT NOT NULL,";
        //        strCreate = strCreate + "MOD_DATE datetime NOT NULL";
        //        strCreate = strCreate + ")";
        //        strCreate = strCreate + "ALTER TABLE " + dt.Rows[0]["TABLE_HISTORY"] + " ADD CONSTRAINT [PK_";
        //        strCreate = strCreate + dt.Rows[0]["TABLE_HISTORY"].ToString().Trim() + "] PRIMARY KEY CLUSTERED ([HIST_ID] ASC )";

        //        Util.WritInfoLog("$ConfigData$", "CreateDimConfigTable", "Query for create table_history", string.Format("Query:{0}", strCreate));
        //        res = sqlConn.SaveDataByFactory(strCreate);
        //        Util.WritInfoLog("$ConfigData$", "CreateDimConfigTable", "End Query for create table_history");

        //        return "true"; 

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog("$ConfigData$", "CreateDimConfigTable", "Error in CreateDimConfigTable", "", ex);
        //        return "false";
        //        throw ex;
        //    }
        //}

        public string GenerateCreateTableString(DataTable dt, Boolean bprimary = false)
        {

            try
            {

                string strCreate = string.Empty;
                string strprimary = string.Empty;

                foreach (DataRow dr in dt.Rows)
                {

                    strCreate = strCreate + dr["FIELD_NAME"] + " " + dr["DATA_TYPE"];
                    if (dr["DATA_TYPE"].ToString().ToLower() == "varchar" || dr["DATA_TYPE"].ToString().ToLower() == "float" || dr["DATA_TYPE"].ToString().ToLower() == "nvarchar" || dr["DATA_TYPE"].ToString().ToLower() == "char")
                    {
                        if (dr["PRECISION"].ToString() != "")
                        {
                            strCreate = strCreate + "(" + dr["PRECISION"] + ")";
                        }                      
                    }

                    if (dr["DATA_TYPE"].ToString().ToLower() == "decimal")
                    {
                        if (dr["PRECISION"].ToString() != "")
                        {
                            strCreate = strCreate + "(" + dr["PRECISION"] + ",2)";
                        }
                        else
                        {
                            strCreate = strCreate + "(18,2)";
                        }
                    }


                    if (Convert.ToString(dr["IS_REQUIRED"]) != "")
                    {
                        if (dr["IS_REQUIRED"].ToString() == "Yes" || Convert.ToInt32(dr["IS_REQUIRED"]) == 1)
                        {
                            strCreate = strCreate + " NOT NULL ";
                        }
                    }

                    //if not null then only
                    if (bprimary == true)
                    {
                        if (Convert.ToString(dr["IS_REQUIRED"]) != "")
                        {
                            if (Convert.ToString(dr["IS_PRIMARY"]) != "")
                            {
                                if (dr["IS_PRIMARY"].ToString() == "Yes" || Convert.ToInt32(dr["IS_PRIMARY"]) == 1)
                                {
                                    //strCreate = strCreate + " PRIMARY KEY ";
                                    if (strprimary == "")
                                    {
                                        strprimary = Convert.ToString(dr["FIELD_NAME"]);
                                    }
                                    else
                                    {
                                        strprimary = strprimary + "," + Convert.ToString(dr["FIELD_NAME"]);
                                    }
                                }
                            }
                        }
                    }

                    strCreate = strCreate + ",";
                }

                strCreate = strCreate.Substring(0, strCreate.Length - 1);

                if (bprimary == true)
                {
                    if (strprimary != "")
                    {
                        //if primary then create unique key
                        //strprimary = " , PRIMARY KEY ( " + strprimary + ")";
                        strprimary = " , UNIQUE ( " + strprimary + ")";
                        strCreate = strCreate + strprimary;
                    }
                }

                return strCreate;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "GenerateCreateTableString", "Error in GenerateCreateTableString", "", ex);
                throw ex;
            }
        }

        public string GenerateCreateTableHist(DataTable dt)
        {

            try
            {

                string strCreate = string.Empty;

                foreach (DataRow dr in dt.Rows)
                {
                    strCreate = strCreate + dr["FIELD_NAME"] + " INT ";
                    if (dr["IS_REQUIRED"].ToString() == "Yes")
                    {
                        strCreate = strCreate + " NOT NULL ";
                    }
                    //if (dr["IS_PRIMARY"].ToString() == "Yes" || Convert.ToInt32(dr["IS_PRIMARY"]) == 1)
                    //{
                    //    strCreate = strCreate + " PRIMARY KEY ";
                    //}

                    strCreate = strCreate + ",";
                }

                strCreate = strCreate.Substring(0, strCreate.Length - 1);
                return strCreate;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "GenerateCreateTableHist", "Error in GenerateCreateTableHist", "", ex);
                throw ex;
            }
        }

        //ReverseEngConfigTable
        public string ReverseEngConfigTable()
        {
            try
            {

                string strTableName = TableName;
                string strListName = ListName;
                string strAbbr = ListName;

                Connection sqlConn = new Connection();

                //get max + 1  id -1
                string strQry = " SELECT MAX(LIST_ID)+ 1 AS LIST_ID FROM X_MASTER_LIST";

                strQry = strQry + " SELECT DISTINCT sys.types.name DATA_TYPE,sys.columns.name ";
                strQry = strQry + " as FIELD_NAME, sys.columns.max_length PRECISION,";
                strQry = strQry + " sys.columns.is_identity, sys.columns.is_nullable IS_NULLABLE, ";
                strQry = strQry + " sys.columns.name as DISPLAY, IS_REQUIRED= ";
                strQry = strQry + " CASE sys.columns.is_nullable WHEN 1 THEN 0  ELSE 1 END   ,";
                strQry = strQry + " ISNULL(i.is_primary_key, 0) IS_PRIMARY,'DROPDOWN_VAL'=null ,'IS_ACTIVE'=1";
                strQry = strQry + " , 'CONTROL_TYPE' = ";
                strQry = strQry + " CASE ";
                strQry = strQry + "    WHEN sys.types.name  = 'bit' THEN 'checkbox'";
                strQry = strQry + "     WHEN sys.types.name  = 'smalldatetime' AND sys.types.name  = 'datetime' THEN 'date'";
                strQry = strQry + "    WHEN sys.types.name  = 'varchar' and sys.types.name  = 'char' ";
                strQry = strQry + "    and sys.types.name  = 'float'and  sys.types.name  = 'char' ";
                strQry = strQry + "     THEN 'textbox'";
                strQry = strQry + "    ELSE 'textbox'";
                strQry = strQry + " END";
                strQry = strQry + " FROM sys.columns    ";
                strQry = strQry + " INNER JOIN sys.types ON sys.columns.system_type_id ";
                strQry = strQry + " =sys.types.system_type_id LEFT OUTER JOIN   ";
                strQry = strQry + "  sys.index_columns ic ON ic.object_id = sys.columns.object_id ";
                strQry = strQry + " AND ic.column_id = sys.columns.column_id    ";
                strQry = strQry + " LEFT OUTER JOIN     sys.indexes i ON ic.object_id = i.object_id  ";
                strQry = strQry + " AND ic.index_id = i.index_id ";
                strQry = strQry + " WHERE sys.columns.object_id = OBJECT_ID('" + strTableName + "')";

                //get max + 1  id of X_MASTER_LIST_COLUMNS
                strQry = strQry + " SELECT MAX(LIST_COL_ID)+ 1 AS LIST_COL_ID FROM X_MASTER_LIST_COLUMNS";

                strQry = strQry + " SELECT sys.types.name DATA_TYPE,sys.columns.name as FIELD_NAME ,";
                strQry = strQry + "ISNULL(i.is_primary_key, 0) IS_PRIMARY ";
                strQry = strQry + " FROM sys.columns  ";
                strQry = strQry + " INNER JOIN sys.types ";
                strQry = strQry + " ON sys.columns.system_type_id =sys.types.system_type_id	";
                strQry = strQry + "LEFT OUTER JOIN    sys.index_columns ic ON ic.object_id = sys.columns.object_id ";
                strQry = strQry + " AND ic.column_id = sys.columns.column_id ";
                strQry = strQry + "LEFT OUTER JOIN     sys.indexes i ON ic.object_id = i.object_id ";
                strQry = strQry + "AND ic.index_id = i.index_id ";
                strQry = strQry + "WHERE sys.columns.object_id = OBJECT_ID('X_MASTER_LIST_COLUMNS')";

                strQry = strQry + " SELECT COUNT(*) CNT FROM X_MASTER_LIST WHERE LIST_NAME='";
                strQry = strQry + ListName + "'";
                strQry = strQry + " OR TABLE_ABBR='" + ListName + "'";
                strQry = strQry + " OR TABLE_NAME='" + strTableName + "'";


                Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "Query for getting details in ReverseEngConfigTable", string.Format("Query:{0}", strQry));
                DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
                Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "End Query for ReverseEngConfigTable");

                if (Convert.ToInt16(ds.Tables[4].Rows[0]["CNT"]) > 0)
                {
                    return "List Name AND ABBR should be unique !";
                }
                else
                {
                    //insert into X_MASTER_LIST
                    string strInsert = "INSERT INTO X_MASTER_LIST";
                    strInsert = strInsert + "(LIST_ID,LIST_NAME,TABLE_NAME,TABLE_ABBR,TABLE_XREF,TABLE_GREY,TABLE_HISTORY) VALUES (";
                    strInsert = strInsert + ds.Tables[0].Rows[0][0] + ",";
                    strInsert = strInsert + "'" + strListName + "',";
                    // strInsert = strInsert + "'M_" + strAbbr + "',";
                    strInsert = strInsert + "'M_" + TableName + "',";
                    strInsert = strInsert + "'" + strAbbr + "',";
                    strInsert = strInsert + "'M_" + strAbbr + "_XREF',";
                    strInsert = strInsert + "'M_" + strAbbr + "_GREY',";
                    strInsert = strInsert + "'M_" + strAbbr + "_HISTORY',";
                    strInsert = strInsert + "'M_" + strAbbr + "_SOURCE',";
                    strInsert = strInsert + "'M_" + strAbbr + "_XREF_TEMP',";
                    strInsert = strInsert + "'M_" + strAbbr + "_XREF_TEMPDETAIL'";
                    strInsert = strInsert + ")";

                    Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "Query for insert into x_master_list", string.Format("Query:{0}", strInsert));
                    Int32 res = sqlConn.SaveDataByFactory(strInsert);
                    Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "End Query for insert into x_master_list");

                    DataTable dt = ds.Tables[1];
                    DataTable dtCol = ds.Tables[3];

                    //insert into X_MASTER_LIST_COLUMNS      
                    DataView DvDim = new DataView(dt);
                    DataView Dv = new DataView(dtCol);

                    int intCol_ID = Convert.ToInt32(ds.Tables[2].Rows[0][0]);
                    int ListId = Convert.ToInt32(ds.Tables[0].Rows[0][0]);

                    string strInsertDim = " INSERT INTO X_MASTER_LIST_COLUMNS ";
                    strInsertDim = strInsertDim + " (LIST_ID,DISPLAY,FIELD_NAME,DATA_TYPE,PRECISION, ";
                    strInsertDim = strInsertDim + " IS_REQUIRED,DROPDOWN_VAL,CONTROL_TYPE,IS_PRIMARY,IS_ACTIVE) ";
                    strInsertDim = strInsertDim + "VALUES (" + ListId + ",";
                    strInsertDim = strInsertDim + ",'DIM_ID','DIM_ID','INT',18,'TRUE',NULL,'textbox','True','True')";

                    Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "Query for insert into x_master_list_col DIM_ID", string.Format("Query:{0}", strInsertDim));
                    res = sqlConn.SaveDataByFactory(strInsertDim);
                    Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "End Query for insert into x_master_list_col DIM_ID");

                    intCol_ID++;

                    Dv.RowFilter = " IS_PRIMARY = FALSE AND FIELD_NAME <> 'LIST_ID'";
                    //Dv.RowFilter = " FIELD_NAME <> 'LIST_ID'";                   

                    foreach (DataRowView drDim in DvDim)
                    {

                        strInsert = "INSERT INTO  X_MASTER_LIST_COLUMNS";

                        string colNm = string.Empty;
                        string colVl = string.Empty;

                        foreach (DataRowView dr in Dv)
                        {

                            colNm = colNm + dr["FIELD_NAME"].ToString() + ",";

                            if (dr["DATA_TYPE"].ToString().ToLower() == "varchar" || dr["DATA_TYPE"].ToString().ToLower() == "char" || dr["DATA_TYPE"].ToString().ToLower() == "nvarchar")
                            {
                                colVl = colVl + "'" + drDim[dr["FIELD_NAME"].ToString()] + "'" + ",";
                            }
                            else if (dr["DATA_TYPE"].ToString().ToLower() == "date")
                            {
                                colVl = colVl + "'" + drDim[dr["FIELD_NAME"].ToString()] + "',"; // Convert.ToDateTime(EndDate).ToString("MM/dd/yyyy")

                            }
                            else if (dr["DATA_TYPE"].ToString().ToLower() == "number" || dr["DATA_TYPE"].ToString().ToLower() == "float" || dr["DATA_TYPE"].ToString().ToLower() == "int")
                            {
                                colVl = colVl + drDim[dr["FIELD_NAME"].ToString()] + ",";

                            }
                            else if (dr["DATA_TYPE"].ToString().ToLower() == "bit")
                            {
                                if (Convert.ToString(drDim[dr["FIELD_NAME"].ToString()]).ToLower() == "false" || Convert.ToString(drDim[dr["FIELD_NAME"].ToString()]).ToLower() == "0")
                                {
                                    colVl = colVl + "0,";
                                }
                                else
                                {
                                    colVl = colVl + "1,";
                                }
                                //colVl = colVl + drDim[dr["FIELD_NAME"].ToString()] + ",";                            
                            }
                        }

                        colNm = colNm.Substring(0, colNm.Length - 1);
                        colVl = colVl.Substring(0, colVl.Length - 1);
                        //Dv.RowFilter = " IS_PRIMARY = TRUE ";

                        strInsert = strInsert + "(LIST_COL_ID,LIST_ID," + colNm + ")";
                        strInsert = strInsert + "VALUES (" + intCol_ID + "," + ListId + "," + colVl + ")";

                        intCol_ID++;

                        Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "Query for insert into x_master_list_col", string.Format("Query:{0}", strInsert));
                        res = sqlConn.SaveDataByFactory(strInsert);
                        Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "End Query for insert into x_master_list_col");

                    }

                    string strCreate = string.Empty;

                    ////create table_name
                    //string strCreate = "CREATE TABLE " + dt.Rows[0]["TABLE_NAME"] + "(";
                    //strCreate = strCreate + GenerateCreateTableString(dt);
                    //strCreate = strCreate + ")";
                    //res = sqlConn.SaveDataByFactory(strCreate);

                    //?????????
                    //what to do for dim_id in table 
                    //if we are using same exisiting table dim_id won't be there. if creating another then if
                    //putting data afterward ,we won't get.

                    ////create table_name
                    //strCreate = "CREATE TABLE M_" + TableName + " (";
                    //strCreate = strCreate + "DIM_ID INT IDENTITY(1,1) NOT NULL,";
                    //strCreate = strCreate + GenerateCreateTableString(dt, true);
                    //strCreate = strCreate + ")";
                    //strCreate = strCreate + "ALTER TABLE M_" + TableName+ " ADD CONSTRAINT [PK_M_";
                    //strCreate = strCreate + TableName + "] PRIMARY KEY CLUSTERED ([DIM_ID] ASC )";

                    //Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "Query for create table", string.Format("Query:{0}", strCreate));
                    //res = sqlConn.SaveDataByFactory(strCreate);
                    //Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "End Query for create table");

                    //create table_xref
                    strCreate = "CREATE TABLE M_" + strAbbr + "_XREF (";
                    strCreate = strCreate + "XREF_ID INT IDENTITY (1,1) NOT NULL,";
                    //   strCreate = strCreate + "DIM_ID INT ,";
                    strCreate = strCreate + GenerateCreateTableString(dt);
                    strCreate = strCreate + ",SOURCE varchar(255) NOT NULL";
                    strCreate = strCreate + ",SOURCE_ID INT NOT NULL,";
                    strCreate = strCreate + "MASTER_ID INT NOT NULL,";
                    strCreate = strCreate + "MANUAL_RULE_ID INT NULL";
                    strCreate = strCreate + ")";
                    strCreate = strCreate + "ALTER TABLE M_" + strAbbr + "_XREF ADD CONSTRAINT [PK_";
                    strCreate = strCreate + "M_" + strAbbr + "_XREF PRIMARY KEY CLUSTERED ([XREF_ID] ASC )";

                    Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "Query for create of xref_table", string.Format("Query:{0}", strCreate));
                    res = sqlConn.SaveDataByFactory(strCreate);
                    Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "End Query for create of xref_table");

                    //create table_xref_temp              
                    strCreate = "CREATE TABLE M_" + strAbbr + "_XREF_TEMP (";
                    strCreate = strCreate + "XREF_ID INT IDENTITY (1,1) NOT NULL,";
                    strCreate = strCreate + GenerateCreateTableString(dt);
                    strCreate = strCreate + ",SOURCE varchar(255) NULL";
                    strCreate = strCreate + ",SOURCE_ID INT NULL,";
                    strCreate = strCreate + "MASTER_ID INT NULL,";
                    strCreate = strCreate + "MANUAL_RULE_ID INT NULL,";
                    strCreate = strCreate + "IMP_ID INT NULL";
                    strCreate = strCreate + ")";
                    strCreate = strCreate + "ALTER TABLE M_" + strAbbr + "_XREF_TEMP ADD CONSTRAINT [PK_";
                    strCreate = strCreate + "M_" + strAbbr + "_XREF_TEMP PRIMARY KEY CLUSTERED ([XREF_ID] ASC )";

                    Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "Query for creating Xref_temp", string.Format("Query:{0}", strCreate));
                    Int32 ret2 = sqlConn.SaveDataByFactory(strCreate);
                    Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "End Query for creating Xref_temp");

                    //create table_xref_TEMPDETAIL              
                    strCreate = "CREATE TABLE M_" + strAbbr + "_XREF_TEMPDETAIL (";
                    strCreate = strCreate + "XREF_TEMP_DETID INT IDENTITY (1,1) NOT NULL,";
                    strCreate = strCreate + "RULE_ID INT NULL,";
                    strCreate = strCreate + "XREF_ID INT NULL,";
                    strCreate = strCreate + "MASTER_ID INT NULL,";
                    strCreate = strCreate + "IMP_ID INT NULL";
                    strCreate = strCreate + ")";
                    strCreate = strCreate + "ALTER TABLE M_" + strAbbr + "_XREF_TEMPDETAIL ADD CONSTRAINT [PK_";
                    strCreate = strCreate + "M_" + strAbbr + "_XREF_TEMPDETAIL PRIMARY KEY CLUSTERED ([XREF_TEMP_DETID] ASC )";

                    Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "Query for table_xref_temp_detail", string.Format("Query:{0}", strCreate));
                    ret2 = sqlConn.SaveDataByFactory(strCreate);
                    Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "End Query for table_xref_temp_detail");

                    //create table_grey
                    strCreate = "CREATE TABLE M_" + strAbbr + "_GREY  (";
                    strCreate = strCreate + "GREY_ID INT IDENTITY (1,1) NOT NULL,";
                    //    strCreate = strCreate + "DIM_ID INT ,";
                    strCreate = strCreate + GenerateCreateTableString(dt);
                    strCreate = strCreate + ",SOURCE varchar(255) NOT NULL";
                    strCreate = strCreate + ",SOURCE_ID INT NOT NULL,";
                    strCreate = strCreate + "MASTER_ID INT NOT NULL";
                    strCreate = strCreate + ")";
                    strCreate = strCreate + "ALTER TABLE M_" + strAbbr + "_GREY ADD CONSTRAINT [PK_";
                    strCreate = strCreate + "M_" + strAbbr + "_GREY PRIMARY KEY CLUSTERED ([GREY_ID] ASC )";

                    Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "Query for create of table_grey", string.Format("Query:{0}", strCreate));
                    res = sqlConn.SaveDataByFactory(strCreate);
                    Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "End Query for create of table_grey");


                    //create table_history
                    strCreate = "CREATE TABLE M_" + strAbbr + "_HISTORY  (";
                    strCreate = strCreate + "HIST_ID INT IDENTITY (1,1) NOT NULL,";
                    //   strCreate = strCreate + "DIM_ID INT ,";
                    strCreate = strCreate + GenerateCreateTableHist(dt);
                    strCreate = strCreate + ",MASTER_ID INT NOT NULL,";
                    strCreate = strCreate + "MOD_DATE datetime NOT NULL";
                    strCreate = strCreate + ")";
                    strCreate = strCreate + "ALTER TABLE M_" + strAbbr + "_HISTORY ADD CONSTRAINT [PK_";
                    strCreate = strCreate + "M_" + strAbbr + "_HISTORY PRIMARY KEY CLUSTERED ([HIST_ID] ASC )";

                    Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "Query for create of table_history", string.Format("Query:{0}", strCreate));
                    res = sqlConn.SaveDataByFactory(strCreate);
                    Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "End Query for create of table_history");


                    //create table_source
                    strCreate = "CREATE TABLE M_" + strAbbr + "_SOURCE  (";
                    strCreate = strCreate + "SOURCE_ID INT IDENTITY (1,1) NOT NULL";
                    strCreate = strCreate + ",CRM INT ";
                    strCreate = strCreate + ",ERP INT ";
                    strCreate = strCreate + ",WEB INT ";
                    strCreate = strCreate + ",MASTER_ID INT NOT NULL";
                    strCreate = strCreate + ")";
                    strCreate = strCreate + "ALTER TABLE M_" + strAbbr + "_SOURCE ADD CONSTRAINT [PK_";
                    strCreate = strCreate + "M_" + strAbbr + "_SOURCE PRIMARY KEY CLUSTERED ([SOURCE_ID] ASC )";

                    Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "Query for create of table_source", string.Format("Query:{0}", strCreate));
                    res = sqlConn.SaveDataByFactory(strCreate);
                    Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "End Query for create of table_source");

                    return "true";
                }

            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "ReverseEngConfigTable", "Error in ReverseEngConfigTable", "", ex);
                return "false";
                throw ex;
            }
        }


        //before SP
        ////ReverseEngConfigTable
        //public string ReverseEngConfigTable()
        //{
        //    try
        //    {

        //        string strTableName = TableName;
        //        string strListName = ListName;
        //        string strAbbr = ListName;

        //        Connection sqlConn = new Connection();

        //        //get max + 1  id -1
        //        string strQry = " SELECT MAX(LIST_ID)+ 1 AS LIST_ID FROM X_MASTER_LIST";

        //        strQry = strQry + " SELECT DISTINCT sys.types.name DATA_TYPE,sys.columns.name ";
        //        strQry = strQry + " as FIELD_NAME, sys.columns.max_length PRECISION,";
        //        strQry = strQry + " sys.columns.is_identity, sys.columns.is_nullable IS_NULLABLE, ";
        //        strQry = strQry + " sys.columns.name as DISPLAY, IS_REQUIRED= ";
        //        strQry = strQry + " CASE sys.columns.is_nullable WHEN 1 THEN 0  ELSE 1 END   ,";
        //        strQry = strQry + " ISNULL(i.is_primary_key, 0) IS_PRIMARY,'DROPDOWN_VAL'=null ,'IS_ACTIVE'=1";
        //        strQry = strQry + " , 'CONTROL_TYPE' = ";
        //        strQry = strQry + " CASE ";
        //        strQry = strQry + "    WHEN sys.types.name  = 'bit' THEN 'checkbox'";
        //        strQry = strQry + "     WHEN sys.types.name  = 'smalldatetime' AND sys.types.name  = 'datetime' THEN 'date'";
        //        strQry = strQry + "    WHEN sys.types.name  = 'varchar' and sys.types.name  = 'char' ";
        //        strQry = strQry + "    and sys.types.name  = 'float'and  sys.types.name  = 'char' ";
        //        strQry = strQry + "     THEN 'textbox'";
        //        strQry = strQry + "    ELSE 'textbox'";
        //        strQry = strQry + " END";
        //        strQry = strQry + " FROM sys.columns    ";
        //        strQry = strQry + " INNER JOIN sys.types ON sys.columns.system_type_id ";
        //        strQry = strQry + " =sys.types.system_type_id LEFT OUTER JOIN   ";
        //        strQry = strQry + "  sys.index_columns ic ON ic.object_id = sys.columns.object_id ";
        //        strQry = strQry + " AND ic.column_id = sys.columns.column_id    ";
        //        strQry = strQry + " LEFT OUTER JOIN     sys.indexes i ON ic.object_id = i.object_id  ";
        //        strQry = strQry + " AND ic.index_id = i.index_id ";
        //        strQry = strQry + " WHERE sys.columns.object_id = OBJECT_ID('" + strTableName + "')";

        //        //get max + 1  id of X_MASTER_LIST_COLUMNS
        //        strQry = strQry + " SELECT MAX(LIST_COL_ID)+ 1 AS LIST_COL_ID FROM X_MASTER_LIST_COLUMNS";

        //        strQry = strQry + " SELECT sys.types.name DATA_TYPE,sys.columns.name as FIELD_NAME ,";
        //        strQry = strQry + "ISNULL(i.is_primary_key, 0) IS_PRIMARY ";
        //        strQry = strQry + " FROM sys.columns  ";
        //        strQry = strQry + " INNER JOIN sys.types ";
        //        strQry = strQry + " ON sys.columns.system_type_id =sys.types.system_type_id	";
        //        strQry = strQry + "LEFT OUTER JOIN    sys.index_columns ic ON ic.object_id = sys.columns.object_id ";
        //        strQry = strQry + " AND ic.column_id = sys.columns.column_id ";
        //        strQry = strQry + "LEFT OUTER JOIN     sys.indexes i ON ic.object_id = i.object_id ";
        //        strQry = strQry + "AND ic.index_id = i.index_id ";
        //        strQry = strQry + "WHERE sys.columns.object_id = OBJECT_ID('X_MASTER_LIST_COLUMNS')";

        //        strQry = strQry + " SELECT COUNT(*) CNT FROM X_MASTER_LIST WHERE LIST_NAME='";
        //        strQry = strQry + ListName + "'";
        //        strQry = strQry + " OR TABLE_ABBR='" + ListName + "'";
        //        strQry = strQry + " OR TABLE_NAME='" + strTableName + "'";


        //        Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "Query for getting details in ReverseEngConfigTable", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "End Query for ReverseEngConfigTable");

        //        if (Convert.ToInt16(ds.Tables[4].Rows[0]["CNT"]) > 0)
        //        {
        //            return "List Name AND ABBR should be unique !";
        //        }
        //        else
        //        {
        //            //insert into X_MASTER_LIST
        //            string strInsert = "INSERT INTO X_MASTER_LIST";
        //            strInsert = strInsert + "(LIST_ID,LIST_NAME,TABLE_NAME,TABLE_ABBR,TABLE_XREF,TABLE_GREY,TABLE_HISTORY) VALUES (";
        //            strInsert = strInsert + ds.Tables[0].Rows[0][0] + ",";
        //            strInsert = strInsert + "'" + strListName + "',";
        //            // strInsert = strInsert + "'M_" + strAbbr + "',";
        //            strInsert = strInsert + "'M_" + TableName + "',";
        //            strInsert = strInsert + "'" + strAbbr + "',";
        //            strInsert = strInsert + "'M_" + strAbbr + "_XREF',";
        //            strInsert = strInsert + "'M_" + strAbbr + "_GREY',";
        //            strInsert = strInsert + "'M_" + strAbbr + "_HISTORY',";
        //            strInsert = strInsert + "'M_" + strAbbr + "_SOURCE',";
        //            strInsert = strInsert + "'M_" + strAbbr + "_XREF_TEMP',";
        //            strInsert = strInsert + "'M_" + strAbbr + "_XREF_TEMPDETAIL'";
        //            strInsert = strInsert + ")";

        //            Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "Query for insert into x_master_list", string.Format("Query:{0}", strInsert));
        //            Int32 res = sqlConn.SaveDataByFactory(strInsert);
        //            Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "End Query for insert into x_master_list");

        //            DataTable dt = ds.Tables[1];
        //            DataTable dtCol = ds.Tables[3];               

        //            //insert into X_MASTER_LIST_COLUMNS      
        //            DataView DvDim = new DataView(dt);
        //            DataView Dv = new DataView(dtCol);

        //            int intCol_ID = Convert.ToInt32(ds.Tables[2].Rows[0][0]);
        //            int ListId = Convert.ToInt32(ds.Tables[0].Rows[0][0]);

        //            string strInsertDim = " INSERT INTO X_MASTER_LIST_COLUMNS ";
        //            strInsertDim = strInsertDim + " (LIST_ID,DISPLAY,FIELD_NAME,DATA_TYPE,PRECISION, ";
        //            strInsertDim = strInsertDim + " IS_REQUIRED,DROPDOWN_VAL,CONTROL_TYPE,IS_PRIMARY,IS_ACTIVE) ";
        //            strInsertDim = strInsertDim + "VALUES (" +  ListId + ",";
        //            strInsertDim = strInsertDim + ",'DIM_ID','DIM_ID','INT',18,'TRUE',NULL,'textbox','True','True')";

        //            Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "Query for insert into x_master_list_col DIM_ID", string.Format("Query:{0}", strInsertDim));
        //            res = sqlConn.SaveDataByFactory(strInsertDim);
        //            Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "End Query for insert into x_master_list_col DIM_ID");

        //            intCol_ID++;

        //            Dv.RowFilter = " IS_PRIMARY = FALSE AND FIELD_NAME <> 'LIST_ID'";
        //            //Dv.RowFilter = " FIELD_NAME <> 'LIST_ID'";                   

        //            foreach (DataRowView drDim in DvDim)
        //            {

        //                strInsert = "INSERT INTO  X_MASTER_LIST_COLUMNS";

        //                string colNm = string.Empty;
        //                string colVl = string.Empty;

        //                foreach (DataRowView dr in Dv)
        //                {

        //                    colNm = colNm + dr["FIELD_NAME"].ToString() + ",";

        //                    if (dr["DATA_TYPE"].ToString().ToLower() == "varchar" || dr["DATA_TYPE"].ToString().ToLower() == "char" || dr["DATA_TYPE"].ToString().ToLower() == "nvarchar")
        //                    {
        //                        colVl = colVl + "'" + drDim[dr["FIELD_NAME"].ToString()] + "'" + ",";
        //                    }
        //                    else if (dr["DATA_TYPE"].ToString().ToLower() == "date")
        //                    {
        //                        colVl = colVl + "'" + drDim[dr["FIELD_NAME"].ToString()] + "',"; // Convert.ToDateTime(EndDate).ToString("MM/dd/yyyy")

        //                    }
        //                    else if (dr["DATA_TYPE"].ToString().ToLower() == "number" || dr["DATA_TYPE"].ToString().ToLower() == "float" || dr["DATA_TYPE"].ToString().ToLower() == "int")
        //                    {
        //                        colVl = colVl + drDim[dr["FIELD_NAME"].ToString()] + ",";

        //                    }
        //                    else if (dr["DATA_TYPE"].ToString().ToLower() == "bit")
        //                    {
        //                        if (Convert.ToString(drDim[dr["FIELD_NAME"].ToString()]).ToLower() == "false" || Convert.ToString(drDim[dr["FIELD_NAME"].ToString()]).ToLower() == "0")
        //                        {
        //                            colVl = colVl + "0,";
        //                        }
        //                        else
        //                        {
        //                            colVl = colVl + "1,";
        //                        }
        //                        //colVl = colVl + drDim[dr["FIELD_NAME"].ToString()] + ",";                            
        //                    }
        //                }

        //                colNm = colNm.Substring(0, colNm.Length - 1);
        //                colVl = colVl.Substring(0, colVl.Length - 1);
        //                //Dv.RowFilter = " IS_PRIMARY = TRUE ";

        //                strInsert = strInsert + "(LIST_COL_ID,LIST_ID," + colNm + ")";
        //                strInsert = strInsert + "VALUES (" + intCol_ID + "," + ListId + "," + colVl + ")";

        //                intCol_ID++;

        //                Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "Query for insert into x_master_list_col", string.Format("Query:{0}", strInsert));
        //                res = sqlConn.SaveDataByFactory(strInsert);
        //                Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "End Query for insert into x_master_list_col");

        //            }

        //            string strCreate = string.Empty;

        //            ////create table_name
        //            //string strCreate = "CREATE TABLE " + dt.Rows[0]["TABLE_NAME"] + "(";
        //            //strCreate = strCreate + GenerateCreateTableString(dt);
        //            //strCreate = strCreate + ")";
        //            //res = sqlConn.SaveDataByFactory(strCreate);

        //            //?????????
        //            //what to do for dim_id in table 
        //            //if we are using same exisiting table dim_id won't be there. if creating another then if
        //            //putting data afterward ,we won't get.

        //            ////create table_name
        //            //strCreate = "CREATE TABLE M_" + TableName + " (";
        //            //strCreate = strCreate + "DIM_ID INT IDENTITY(1,1) NOT NULL,";
        //            //strCreate = strCreate + GenerateCreateTableString(dt, true);
        //            //strCreate = strCreate + ")";
        //            //strCreate = strCreate + "ALTER TABLE M_" + TableName+ " ADD CONSTRAINT [PK_M_";
        //            //strCreate = strCreate + TableName + "] PRIMARY KEY CLUSTERED ([DIM_ID] ASC )";

        //            //Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "Query for create table", string.Format("Query:{0}", strCreate));
        //            //res = sqlConn.SaveDataByFactory(strCreate);
        //            //Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "End Query for create table");

        //            //create table_xref
        //            strCreate = "CREATE TABLE M_" + strAbbr + "_XREF (";
        //            strCreate = strCreate + "XREF_ID INT IDENTITY (1,1) NOT NULL,";
        //            //   strCreate = strCreate + "DIM_ID INT ,";
        //            strCreate = strCreate + GenerateCreateTableString(dt);
        //            strCreate = strCreate + ",SOURCE varchar(255) NOT NULL";
        //            strCreate = strCreate + ",SOURCE_ID INT NOT NULL,";
        //            strCreate = strCreate + "MASTER_ID INT NOT NULL,";
        //            strCreate = strCreate + "MANUAL_RULE_ID INT NULL";
        //            strCreate = strCreate + ")";
        //            strCreate = strCreate + "ALTER TABLE M_" + strAbbr + "_XREF ADD CONSTRAINT [PK_";
        //            strCreate = strCreate + "M_" + strAbbr + "_XREF PRIMARY KEY CLUSTERED ([XREF_ID] ASC )";

        //            Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "Query for create of xref_table", string.Format("Query:{0}", strCreate));
        //            res = sqlConn.SaveDataByFactory(strCreate);
        //            Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "End Query for create of xref_table");

        //            //create table_xref_temp              
        //            strCreate = "CREATE TABLE M_" + strAbbr + "_XREF_TEMP (";
        //            strCreate = strCreate + "XREF_ID INT IDENTITY (1,1) NOT NULL,";
        //            strCreate = strCreate + GenerateCreateTableString(dt);
        //            strCreate = strCreate + ",SOURCE varchar(255) NULL";
        //            strCreate = strCreate + ",SOURCE_ID INT NULL,";
        //            strCreate = strCreate + "MASTER_ID INT NULL,";
        //            strCreate = strCreate + "MANUAL_RULE_ID INT NULL,";
        //            strCreate = strCreate + "IMP_ID INT NULL";
        //            strCreate = strCreate + ")";
        //            strCreate = strCreate + "ALTER TABLE M_" + strAbbr + "_XREF_TEMP ADD CONSTRAINT [PK_";
        //            strCreate = strCreate + "M_" + strAbbr + "_XREF_TEMP PRIMARY KEY CLUSTERED ([XREF_ID] ASC )";

        //            Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "Query for creating Xref_temp", string.Format("Query:{0}", strCreate));
        //            Int32 ret2 = sqlConn.SaveDataByFactory(strCreate);
        //            Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "End Query for creating Xref_temp");

        //            //create table_xref_TEMPDETAIL              
        //            strCreate = "CREATE TABLE M_" + strAbbr + "_XREF_TEMPDETAIL (";
        //            strCreate = strCreate + "XREF_TEMP_DETID INT IDENTITY (1,1) NOT NULL,";
        //            strCreate = strCreate + "RULE_ID INT NULL,";
        //            strCreate = strCreate + "XREF_ID INT NULL,";
        //            strCreate = strCreate + "MASTER_ID INT NULL,";
        //            strCreate = strCreate + "IMP_ID INT NULL";
        //            strCreate = strCreate + ")";
        //            strCreate = strCreate + "ALTER TABLE M_" + strAbbr + "_XREF_TEMPDETAIL ADD CONSTRAINT [PK_";
        //            strCreate = strCreate + "M_" + strAbbr + "_XREF_TEMPDETAIL PRIMARY KEY CLUSTERED ([XREF_TEMP_DETID] ASC )";

        //            Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "Query for table_xref_temp_detail", string.Format("Query:{0}", strCreate));
        //            ret2 = sqlConn.SaveDataByFactory(strCreate);
        //            Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "End Query for table_xref_temp_detail");

        //            //create table_grey
        //            strCreate = "CREATE TABLE M_" + strAbbr + "_GREY  (";
        //            strCreate = strCreate + "GREY_ID INT IDENTITY (1,1) NOT NULL,";
        //            //    strCreate = strCreate + "DIM_ID INT ,";
        //            strCreate = strCreate + GenerateCreateTableString(dt);
        //            strCreate = strCreate + ",SOURCE varchar(255) NOT NULL";
        //            strCreate = strCreate + ",SOURCE_ID INT NOT NULL,";
        //            strCreate = strCreate + "MASTER_ID INT NOT NULL";
        //            strCreate = strCreate + ")";
        //            strCreate = strCreate + "ALTER TABLE M_" + strAbbr + "_GREY ADD CONSTRAINT [PK_";
        //            strCreate = strCreate + "M_" + strAbbr + "_GREY PRIMARY KEY CLUSTERED ([GREY_ID] ASC )";

        //            Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "Query for create of table_grey", string.Format("Query:{0}", strCreate));
        //            res = sqlConn.SaveDataByFactory(strCreate);
        //            Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "End Query for create of table_grey");


        //            //create table_history
        //            strCreate = "CREATE TABLE M_" + strAbbr + "_HISTORY  (";
        //            strCreate = strCreate + "HIST_ID INT IDENTITY (1,1) NOT NULL,";
        //            //   strCreate = strCreate + "DIM_ID INT ,";
        //            strCreate = strCreate + GenerateCreateTableHist(dt);
        //            strCreate = strCreate + ",MASTER_ID INT NOT NULL,";
        //            strCreate = strCreate + "MOD_DATE datetime NOT NULL";
        //            strCreate = strCreate + ")";
        //            strCreate = strCreate + "ALTER TABLE M_" + strAbbr + "_HISTORY ADD CONSTRAINT [PK_";
        //            strCreate = strCreate + "M_" + strAbbr + "_HISTORY PRIMARY KEY CLUSTERED ([HIST_ID] ASC )";

        //            Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "Query for create of table_history", string.Format("Query:{0}", strCreate));
        //            res = sqlConn.SaveDataByFactory(strCreate);
        //            Util.WritInfoLog("$ConfigData$", "ReverseEngConfigTable", "End Query for create of table_history");


        //            return "true";
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog("$ConfigData$", "ReverseEngConfigTable", "Error in ReverseEngConfigTable", "", ex);
        //        return "false";
        //        throw ex;
        //    }
        //}

        public string bind_reEng()
        {
            try
            {

                Connection sqlConn = new Connection();
    
                Util.WritInfoLog("$ConfigData$", "bind_reEng", "method for bind_reEng", string.Format("method:{0}", ""));
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_bindReEng");
                Util.WritInfoLog("$ConfigData$", "bind_reEng", "End method for bind_reEng");

                List<DropdownListItem> listMaster = new List<DropdownListItem>();
                listMaster.Add(new DropdownListItem("--Select--", ""));
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    // listMaster.Add(new DropdownListItem(dr["HIER_NAME"].ToString(), dr["HIERARCHY_ID"].ToString(), dr["NODE_TABLE_NAME"].ToString()));
                    listMaster.Add(new DropdownListItem(dr["NAME"].ToString(), dr["NAME"].ToString()));
                }

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(listMaster);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "bind_reEng", "Error in bind_reEng", "", ex);
                throw ex;
            }
        }

        //before SP
        //public string bind_reEng()
        //{
        //    try
        //    {

        //        Connection sqlConn = new Connection();

        //        string strQry = " SELECT NAME FROM sys.tables WHERE ";
        //        strQry = strQry + " name NOT LIKE 'M[_]%' ";
        //        strQry = strQry + "  AND name NOT LIKE 'X[_]%'";
        //        strQry = strQry + " AND name NOT LIKE 'C[_]___[_]%' ";
        //        strQry = strQry + "  AND name NOT LIKE 'DD[_]%'";
        //        strQry = strQry + " AND NOT EXISTS ";
        //        strQry = strQry + "(SELECT NULL from X_MASTER_LIST WHERE X_MASTER_LIST.TABLE_NAME ";
        //        strQry = strQry + "=sys.tables.name) ";

        //        Util.WritInfoLog("$ConfigData$", "bind_reEng", "Query for bind_reEng", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog("$ConfigData$", "bind_reEng", "End Query for bind_reEng");

        //        List<DropdownListItem> listMaster = new List<DropdownListItem>();
        //        listMaster.Add(new DropdownListItem("--Select--", ""));
        //        foreach (DataRow dr in ds.Tables[0].Rows)
        //        {
        //            // listMaster.Add(new DropdownListItem(dr["HIER_NAME"].ToString(), dr["HIERARCHY_ID"].ToString(), dr["NODE_TABLE_NAME"].ToString()));
        //            listMaster.Add(new DropdownListItem(dr["NAME"].ToString(), dr["NAME"].ToString()));
        //        }

        //        JavaScriptSerializer js = new JavaScriptSerializer();
        //        return js.Serialize(listMaster);

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog("$ConfigData$", "bind_reEng", "Error in bind_reEng", "", ex);
        //        throw ex;
        //    }
        //}

        public string get_griddata()
        {

            try
            {

                // string strTableName = "M_EMPP";
                //string strListName = "EMPP";
                // string strAbbr = "EMPP";

                string strTableName = TableName;
                string strListName = ListName;
                string strAbbr = ListName;

                Connection sqlConn = new Connection();
                string strQry;
                //get max + 1  id -1
                //string strQry = " SELECT MAX(LIST_ID)+ 1 AS LIST_ID FROM X_MASTER_LIST";
                strQry = " SELECT DISTINCT sys.types.name DATA_TYPE,sys.columns.name ";
                strQry = strQry + " as FIELD_NAME, sys.columns.max_length PRECISION,";
                strQry = strQry + " sys.columns.is_identity, sys.columns.is_nullable IS_NULLABLE, ";
                strQry = strQry + " sys.columns.name as DISPLAY, IS_REQUIRED= ";
                strQry = strQry + " CASE sys.columns.is_nullable WHEN 1 THEN 0  ELSE 1 END   ,";
                strQry = strQry + " ISNULL(i.is_primary_key, 0) IS_PRIMARY,'DROPDOWN_VAL'=null ,'IS_ACTIVE'=1";
                strQry = strQry + " , 'CONTROL_TYPE' = ";
                strQry = strQry + " CASE ";
                strQry = strQry + "    WHEN sys.types.name  = 'bit' THEN 'checkbox'";
                strQry = strQry + "     WHEN sys.types.name  = 'smalldatetime' AND sys.types.name  = 'datetime' THEN 'date'";
                strQry = strQry + "    WHEN sys.types.name  = 'varchar' and sys.types.name  = 'char' ";
                strQry = strQry + "    and sys.types.name  = 'float'and  sys.types.name  = 'char' ";
                strQry = strQry + "     THEN 'textbox'";
                strQry = strQry + "    ELSE 'textbox'";
                strQry = strQry + " END";
                strQry = strQry + " FROM sys.columns    ";
                strQry = strQry + " INNER JOIN sys.types ON sys.columns.system_type_id ";
                strQry = strQry + " =sys.types.system_type_id LEFT OUTER JOIN   ";
                strQry = strQry + "  sys.index_columns ic ON ic.object_id = sys.columns.object_id ";
                strQry = strQry + " AND ic.column_id = sys.columns.column_id    ";
                strQry = strQry + " LEFT OUTER JOIN     sys.indexes i ON ic.object_id = i.object_id  ";
                strQry = strQry + " AND ic.index_id = i.index_id ";
                strQry = strQry + " WHERE sys.columns.object_id = OBJECT_ID('" + strTableName + "')";

                strQry = strQry + " SELECT * FROM " + strTableName;

                Util.WritInfoLog("$ConfigData$", "get_griddata", "Query for get_griddata", string.Format("Query:{0}", strQry));
                DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
                Util.WritInfoLog("$ConfigData$", "get_griddata", "End Query for get_griddata");

                DataTable dt = ds.Tables[0];

                Grid griddata = new Grid();
                //set columns,colmodel,primary key
                griddata = SetColModel(griddata, dt);

                List<object> rows = SetDatarow(ds.Tables[1]);
                griddata.datarow = rows;

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(griddata);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "get_griddata", "Error in get_griddata", "", ex);
                return "false";
                throw ex;
            }
        }
      
        public string get_UserTypeCol()
        {
            try
            {

                string primarykey = string.Empty;
                Connection sqlConn = new Connection();

                Util.WritInfoLog("$ConfigData$", "get_UserTypeCol", "proc for get_UserTypeCol", "usp_GetConfigUserType");
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetConfigUserType");
                Util.WritInfoLog("$ConfigData$", "get_UserTypeCol", "End proc for get_UserTypeCol");

                DataTable dt = ds.Tables[0];

                Grid griddata = new Grid();
                //set columns,colmodel,primary key
                griddata = SetColModel(griddata, ds.Tables[1]); 

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(griddata);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "get_UserType", "Error in get_UserType", "", ex);
                throw ex;
            }
        }

        //with SP
        public string get_UserTypeData()
        {
            try
            {

                string primarykey = string.Empty;
                Connection sqlConn = new Connection();

                Util.WritInfoLog("$ConfigData$", "get_UserTypeData", "proc for getting details in get_UserTypeData", "usp_GetConfigUserType");
                DataSet dsCol = sqlConn.GetDataSetFromFactoryProc("usp_GetConfigUserType");
                Util.WritInfoLog("$ConfigData$", "get_UserTypeData", "End proc for getting details in get_UserTypeData");

                DataTable dtCol = dsCol.Tables[1];

                string strData = string.Empty;

                if (dtCol.Rows.Count > 0)
                {

                    DataView dv = new DataView(dtCol);
                    dv.RowFilter = " IS_PRIMARY =TRUE";

                    if (sidx == "" || sidx == null)
                    {
                        sidx = dv[0]["FIELD_NAME"].ToString();
                    }

                    string sortExp = sidx + " " + sord;
                    int start = ((page - 1) * rows);

                    List<DBParameter> lstparam = new List<DBParameter>();
                    lstparam.Add(new DBParameter("@start", DbType.Int32, ParameterDirection.Input, 0, start));
                    lstparam.Add(new DBParameter("@rows", DbType.Int32, ParameterDirection.Input, 0, rows));
                    lstparam.Add(new DBParameter("@query", DbType.String, ParameterDirection.Input, 1000, query.Replace("\"", "'")));
                    lstparam.Add(new DBParameter("@sortExp", DbType.String, ParameterDirection.Input, 1000, sortExp));
                    lstparam.Add(new DBParameter("@_search", DbType.Boolean, ParameterDirection.Input, 0, _search));

                    Util.WritInfoLog(ClsName, "get_UserTypeData", "method for get_UserTypeData","usp_GetUserTypeData");
                    DataSet dsdata = sqlConn.GetDataSetFromFactoryProc("usp_GetUserTypeData", ref(lstparam));
                    Util.WritInfoLog(ClsName, "get_UserTypeData", "End method for get_UserTypeData");
                 
                    //do for paging 
                    DataTable dtData = dsdata.Tables[0];
                    dtData.Columns.Remove("Row");

                    int cnt = 0;
                    cnt = Convert.ToInt32(dsdata.Tables[1].Rows[0]["CNT"]);
                    strData = Common.JsonForJqgrid(dtData, rows, cnt, page);
                }

                JavaScriptSerializer js = new JavaScriptSerializer();
                return strData;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "get_UserType", "Error in get_UserType", "", ex);
                throw ex;
            }
        }
        
        //before SP
        //public string get_UserTypeData()
        //{
        //    try
        //    {

        //        string primarykey = string.Empty;

        //        Connection sqlConn = new Connection();

        //        Util.WritInfoLog("$ConfigData$", "get_UserTypeData", "proc for getting details in get_UserTypeData", "usp_GetConfigUserType");
        //        DataSet dsCol = sqlConn.GetDataSetFromFactoryProc("usp_GetConfigUserType");
        //        Util.WritInfoLog("$ConfigData$", "get_UserTypeData", "End proc for getting details in get_UserTypeData");


        //        DataTable dtCol = dsCol.Tables[1];

        //        string strData = string.Empty;           

        //        if (dtCol.Rows.Count > 0)
        //        {

        //            DataView dv = new DataView(dtCol);

        //            dv.RowFilter = " IS_PRIMARY =TRUE";

        //            if (sidx == "" || sidx == null)
        //            {
        //                sidx = dv[0]["FIELD_NAME"].ToString();
        //            }

        //            string sortExp = sidx + " " + sord;
        //            int start = ((page - 1) * rows);

        //            string strQryy = " SELECT DISTINCT * FROM (";
        //            strQryy = strQryy + " SELECT ROW_NUMBER() OVER (ORDER BY " + sortExp + ") AS Row,";
        //            strQryy = strQryy + " M_USER_TYPE.*,hasUser=  ";
        //            strQryy = strQryy + " CASE WHEN dbo.udf_hasUser(USER_TYPE_ID)>0  THEN 1 ELSE 0  END ";
        //            strQryy = strQryy + " FROM  M_USER_TYPE";
        //            //for search filter
        //            if (_search && (query != null || query != ""))
        //            {
        //                strQryy = strQryy + " WHERE " + query.Replace("\"", "'");//errString.Replace("docment", "document");
        //            }
        //            strQryy = strQryy + ") tmp ";
        //            strQryy = strQryy + " WHERE Row > " + start + " AND Row <= " + (start + rows);

        //            strQryy = strQryy + " SELECT COUNT(*) CNT FROM  M_USER_TYPE ";
        //            //for search filter
        //            if (_search && (query != null || query != ""))
        //            {
        //                strQryy = strQryy + " WHERE " + query.Replace("\"", "'");//errString.Replace("docment", "document");
        //            }

        //            Util.WritInfoLog("$ConfigData$", "GetXMasterListColumns", "Query for GetXMasterListColumns", string.Format("Query:{0}", strQryy));
        //            DataSet dsdata = sqlConn.GetDataSetFromFactory(strQryy);
        //            Util.WritInfoLog("$ConfigData$", "GetXMasterListColumns", "End Query for GetXMasterListColumns");


        //            //do for paging 
        //            DataTable dtData = dsdata.Tables[0];

        //            dtData.Columns.Remove("Row");

        //            int cnt = 0;
        //            cnt = Convert.ToInt32(dsdata.Tables[1].Rows[0]["CNT"]);
        //            strData = Common.JsonForJqgrid(dtData, rows, cnt, page);
        //        }

        //        JavaScriptSerializer js = new JavaScriptSerializer();
        //        return strData;


        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog("$ConfigData$", "get_UserType", "Error in get_UserType", "", ex);
        //        throw ex;
        //    }
        //}

        public string get_UserDataCol()
        {
            try
            {

                string primarykey = string.Empty;
                Connection sqlConn = new Connection();

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@USER_TYPE_ID", DbType.String, ParameterDirection.Input, 255,UserTypeId));

                Util.WritInfoLog("$ConfigData$", "get_UserDataCol", "method for get_UserDataCol", string.Format("method:{0}", "usp_GetUserDataCol"));
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetUserDataCol", ref(lstparam));
                Util.WritInfoLog("$ConfigData$", "get_UserDataCol", "End method for get_UserDataCol");

                DataTable dt = ds.Tables[0];
                DataTable dtcol = ds.Tables[1];
                DataView dv = new DataView(dtcol);

                dv.RowFilter = " FIELD_NAME <> 'USER_TYPE_ID'";
                DataTable dtfilter = dv.ToTable();

                Grid griddata = new Grid();
                //set columns,colmodel,primary key
                griddata = SetColModel(griddata, dtfilter);

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(griddata);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "get_UserData", "Error in get_UserData", "", ex);
                throw ex;
            }
        }

        public string get_UserData()
        {
            try
            {

                string primarykey = string.Empty;

                Connection sqlConn = new Connection();
                string strData = string.Empty;

                List<DBParameter> lstparam1 = new List<DBParameter>();
                lstparam1.Add(new DBParameter("@USER_TYPE_ID", DbType.String, ParameterDirection.Input, 255, UserTypeId));

                Util.WritInfoLog("$ConfigData$", "get_UserData", "method for getting col details in get_UserData", string.Format("method:{0}", "usp_GetUserDataCol"));
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetUserDataCol", ref(lstparam1));
                Util.WritInfoLog("$ConfigData$", "get_UserData", "End method for getting col details in get_UserData");

                DataTable dt = ds.Tables[0];
                DataTable dtcol = ds.Tables[1];
                DataView dv = new DataView(dtcol);

                dv.RowFilter = " FIELD_NAME <> 'USER_TYPE_ID'";
                DataTable dtfilter = dv.ToTable();

                if (dtcol.Rows.Count > 0)
                {

                    DataView dvP = new DataView(dtcol);
                    dvP.RowFilter = " IS_PRIMARY =TRUE";

                    if (sidx == "" || sidx == null)
                    {
                        sidx = dvP[0]["FIELD_NAME"].ToString();
                    }

                    string sortExp = sidx + " " + sord;
                    int start = ((page - 1) * rows);

                    List<DBParameter> lstparam = new List<DBParameter>();
                    lstparam.Add(new DBParameter("@start", DbType.Int32, ParameterDirection.Input, 0, start));
                    lstparam.Add(new DBParameter("@rows", DbType.Int32, ParameterDirection.Input, 0, rows));
                    lstparam.Add(new DBParameter("@query", DbType.String, ParameterDirection.Input, 1000, query.Replace("\"", "'")));
                    lstparam.Add(new DBParameter("@sortExp", DbType.String, ParameterDirection.Input, 1000, sortExp));
                    lstparam.Add(new DBParameter("@_search", DbType.Boolean, ParameterDirection.Input, 0, _search));
                    lstparam.Add(new DBParameter("@USER_TYPE_ID", DbType.Int32, ParameterDirection.Input, 0, UserTypeId));

                    Util.WritInfoLog(ClsName, "get_UserData", "method for get_UserData");
                    DataSet dsdata = sqlConn.GetDataSetFromFactoryProc("usp_GetUserData", ref(lstparam));
                    Util.WritInfoLog(ClsName, "get_UserData", "End method for get_UserData");

                    //do for paging 
                    DataTable dtData = dsdata.Tables[0];
                    dtData.Columns.Remove("Row");
                    dtData.Columns.Remove("USER_TYPE_ID");

                    int cnt = 0;
                    cnt = Convert.ToInt32(dsdata.Tables[1].Rows[0]["CNT"]);
                    strData = Common.JsonForJqgrid(dtData, rows, cnt, page);
                }

                JavaScriptSerializer js = new JavaScriptSerializer();
                return strData;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "get_UserData", "Error in get_UserData", "", ex);
                throw ex;
            }
        }

        //before SP
        //public string get_UserData()
        //{
        //    try
        //    {

        //        string primarykey = string.Empty;

        //        Connection sqlConn = new Connection();
        //        string strData = string.Empty;

        //        string strQry = " SELECT * FROM M_USER ";
        //        strQry = strQry + " WHERE USER_TYPE_ID = " + UserTypeId;
        //        strQry = strQry + " ORDER BY USER_ID ASC ";

        //        strQry = strQry + "    SELECT sys.columns.name as DISPLAY, ";
        //        strQry = strQry + "     sys.columns.name as FIELD_NAME ,sys.types.name DATA_TYPE, ";
        //        strQry = strQry + "   sys.columns.max_length PRECISION,  ";
        //        strQry = strQry + "    IS_REQUIRED= CASE sys.columns.is_nullable WHEN 1 THEN 0  ELSE 1 END ";
        //        strQry = strQry + "   ,ISNULL(i.is_primary_key, 0)IS_PRIMARY,sys.columns.is_nullable IS_NULLABLE, ";
        //        strQry = strQry + "  sys.columns.is_identity FROM sys.columns  ";
        //        strQry = strQry + "   INNER JOIN sys.types ON sys.columns.system_type_id ";
        //        strQry = strQry + "   =sys.types.system_type_id ";
        //        strQry = strQry + "   LEFT OUTER JOIN    sys.index_columns ic ON ic.object_id ";
        //        strQry = strQry + "    = sys.columns.object_id  AND ic.column_id = sys.columns.column_id  ";
        //        strQry = strQry + "    LEFT OUTER JOIN     sys.indexes i ON ic.object_id = i.object_id ";
        //        strQry = strQry + "    AND ic.index_id = i.index_id  WHERE sys.columns.object_id ";
        //        strQry = strQry + "   = OBJECT_ID('M_USER') ";
        //        strQry = strQry + " order by sys.columns.column_id ";

        //        Util.WritInfoLog("$ConfigData$", "get_UserData", "Query for getting columns data in get_UserData", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog("$ConfigData$", "get_UserData", "End Query for getting columns data in get_UserData");

        //        DataTable dt = ds.Tables[0];
        //        DataTable dtcol = ds.Tables[1];
        //        DataView dv = new DataView(dtcol);

        //        dv.RowFilter = " FIELD_NAME <> 'USER_TYPE_ID'";
        //        DataTable dtfilter = dv.ToTable();

        //        if (dtcol.Rows.Count > 0)
        //        {

        //            DataView dvP = new DataView(dtcol);
        //            dvP.RowFilter = " IS_PRIMARY =TRUE";

        //            if (sidx == "" || sidx == null)
        //            {
        //                sidx = dvP[0]["FIELD_NAME"].ToString();
        //            }

        //            string sortExp = sidx + " " + sord;
        //            int start = ((page - 1) * rows);

        //            string strQryy = " SELECT * FROM (";
        //            strQryy = strQryy + " SELECT ROW_NUMBER() OVER (ORDER BY " + sortExp + ") AS Row,";
        //            strQryy = strQryy + " * ";
        //            strQryy = strQryy + " FROM  M_USER ";
        //            strQryy = strQryy + " WHERE USER_TYPE_ID = " + UserTypeId;
        //            //for search filter
        //            if (_search && (query != null || query != ""))
        //            {
        //                strQryy = strQryy + " AND " + query.Replace("\"", "'");
        //            }
        //            strQryy = strQryy + ") tmp ";
        //            strQryy = strQryy + " WHERE Row > " + start + " AND Row <= " + (start + rows);

        //            strQryy = strQryy + " SELECT COUNT(*) CNT FROM  M_USER ";
        //            strQryy = strQryy + " WHERE USER_TYPE_ID = " + UserTypeId;
        //            //for search filter
        //            if (_search && (query != null || query != ""))
        //            {
        //                strQryy = strQryy + " WHERE " + query.Replace("\"", "'");
        //            }

        //            Util.WritInfoLog("$ConfigData$", "GetXMasterListColumns", "Query for GetXMasterListColumns", string.Format("Query:{0}", strQryy));
        //            DataSet dsdata = sqlConn.GetDataSetFromFactory(strQryy);
        //            Util.WritInfoLog("$ConfigData$", "GetXMasterListColumns", "End Query for GetXMasterListColumns");

        //            //do for paging 
        //            DataTable dtData = dsdata.Tables[0];
        //            dtData.Columns.Remove("Row");
        //            dtData.Columns.Remove("USER_TYPE_ID");

        //            int cnt = 0;
        //            cnt = Convert.ToInt32(dsdata.Tables[1].Rows[0]["CNT"]);
        //            strData = Common.JsonForJqgrid(dtData, rows, cnt, page);
        //        }

        //        JavaScriptSerializer js = new JavaScriptSerializer();
        //        return strData;

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog("$ConfigData$", "get_UserData", "Error in get_UserData", "", ex);
        //        throw ex;
        //    }
        //}


        //before SP
        //public string get_UserDataCol()
        //{
        //    try
        //    {

        //        string primarykey = string.Empty;
        //        Connection sqlConn = new Connection();

        //        string strQry = " SELECT * FROM M_USER ";
        //        strQry = strQry + " WHERE USER_TYPE_ID = " + UserTypeId;
        //        strQry = strQry + " ORDER BY USER_ID ASC ";            

        //        strQry = strQry + "    SELECT sys.columns.name as DISPLAY, ";
        //        strQry = strQry + "     sys.columns.name as FIELD_NAME ,sys.types.name DATA_TYPE, ";
        //        strQry = strQry + "   sys.columns.max_length PRECISION,  ";
        //        strQry = strQry + "    IS_REQUIRED= CASE sys.columns.is_nullable WHEN 1 THEN 0  ELSE 1 END ";
        //        strQry = strQry + "   ,ISNULL(i.is_primary_key, 0)IS_PRIMARY,sys.columns.is_nullable IS_NULLABLE, ";
        //        strQry = strQry + "  sys.columns.is_identity FROM sys.columns  ";
        //        strQry = strQry + "   INNER JOIN sys.types ON sys.columns.system_type_id ";
        //        strQry = strQry + "   =sys.types.system_type_id ";
        //        strQry = strQry + "   LEFT OUTER JOIN    sys.index_columns ic ON ic.object_id ";
        //        strQry = strQry + "    = sys.columns.object_id  AND ic.column_id = sys.columns.column_id  ";
        //        strQry = strQry + "    LEFT OUTER JOIN     sys.indexes i ON ic.object_id = i.object_id ";
        //        strQry = strQry + "    AND ic.index_id = i.index_id  WHERE sys.columns.object_id ";
        //        strQry = strQry + "   = OBJECT_ID('M_USER') ";
        //        strQry = strQry + " order by sys.columns.column_id ";

        //        Util.WritInfoLog("$ConfigData$", "get_UserDataCol", "Query for get_UserDataCol", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog("$ConfigData$", "get_UserDataCol", "End Query for get_UserDataCol");

        //        DataTable dt = ds.Tables[0];
        //        DataTable dtcol = ds.Tables[1];
        //        DataView dv = new DataView(dtcol);

        //        dv.RowFilter = " FIELD_NAME <> 'USER_TYPE_ID'";
        //        DataTable dtfilter = dv.ToTable();

        //        Grid griddata = new Grid();
        //        //set columns,colmodel,primary key
        //        griddata = SetColModel(griddata, dtfilter);
      
        //        JavaScriptSerializer js = new JavaScriptSerializer();
        //        return js.Serialize(griddata);

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog("$ConfigData$", "get_UserData", "Error in get_UserData", "", ex);
        //        throw ex;
        //    }
        //}

        //public string get_UserData()
        //{
        //    try
        //    {

        //        string primarykey = string.Empty;

        //        Connection sqlConn = new Connection();
        //        string strData = string.Empty;

        //        string strQry = " SELECT * FROM M_USER ";
        //        strQry = strQry + " WHERE USER_TYPE_ID = " + UserTypeId;
        //        strQry = strQry + " ORDER BY USER_ID ASC ";

        //        strQry = strQry + "    SELECT sys.columns.name as DISPLAY, ";
        //        strQry = strQry + "     sys.columns.name as FIELD_NAME ,sys.types.name DATA_TYPE, ";
        //        strQry = strQry + "   sys.columns.max_length PRECISION,  ";
        //        strQry = strQry + "    IS_REQUIRED= CASE sys.columns.is_nullable WHEN 1 THEN 0  ELSE 1 END ";
        //        strQry = strQry + "   ,ISNULL(i.is_primary_key, 0)IS_PRIMARY,sys.columns.is_nullable IS_NULLABLE, ";
        //        strQry = strQry + "  sys.columns.is_identity FROM sys.columns  ";
        //        strQry = strQry + "   INNER JOIN sys.types ON sys.columns.system_type_id ";
        //        strQry = strQry + "   =sys.types.system_type_id ";
        //        strQry = strQry + "   LEFT OUTER JOIN    sys.index_columns ic ON ic.object_id ";
        //        strQry = strQry + "    = sys.columns.object_id  AND ic.column_id = sys.columns.column_id  ";
        //        strQry = strQry + "    LEFT OUTER JOIN     sys.indexes i ON ic.object_id = i.object_id ";
        //        strQry = strQry + "    AND ic.index_id = i.index_id  WHERE sys.columns.object_id ";
        //        strQry = strQry + "   = OBJECT_ID('M_USER') ";
        //        strQry = strQry + " order by sys.columns.column_id ";

        //        Util.WritInfoLog("$ConfigData$", "get_UserData", "Query for getting columns data in get_UserData", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog("$ConfigData$", "get_UserData", "End Query for getting columns data in get_UserData");

        //        DataTable dt = ds.Tables[0];
        //        DataTable dtcol = ds.Tables[1];
        //        DataView dv = new DataView(dtcol);

        //        dv.RowFilter = " FIELD_NAME <> 'USER_TYPE_ID'";
        //        DataTable dtfilter = dv.ToTable();

        //        if (dtcol.Rows.Count > 0)
        //        {

        //            DataView dvP = new DataView(dtcol);
        //            dvP.RowFilter = " IS_PRIMARY =TRUE";             

        //            if (sidx == "" || sidx == null)
        //            {
        //                sidx = dvP[0]["FIELD_NAME"].ToString();
        //            }

        //            string sortExp = sidx + " " + sord;
        //            int start = ((page - 1) * rows);

        //            string strQryy = " SELECT * FROM (";
        //            strQryy = strQryy + " SELECT ROW_NUMBER() OVER (ORDER BY " + sortExp + ") AS Row,";
        //            strQryy = strQryy + " * ";
        //            strQryy = strQryy + " FROM  M_USER ";
        //            strQryy = strQryy + " WHERE USER_TYPE_ID = " + UserTypeId;
        //            //for search filter
        //            if (_search && (query != null || query != ""))
        //            {
        //                strQryy = strQryy + " AND " + query.Replace("\"", "'");
        //            }
        //            strQryy = strQryy + ") tmp ";
        //            strQryy = strQryy + " WHERE Row > " + start + " AND Row <= " + (start + rows);

        //            strQryy = strQryy + " SELECT COUNT(*) CNT FROM  M_USER ";
        //            strQryy = strQryy + " WHERE USER_TYPE_ID = " + UserTypeId;
        //            //for search filter
        //            if (_search && (query != null || query != ""))
        //            {
        //                strQryy = strQryy + " WHERE " + query.Replace("\"", "'");
        //            }

        //            Util.WritInfoLog("$ConfigData$", "GetXMasterListColumns", "Query for GetXMasterListColumns", string.Format("Query:{0}", strQryy));
        //            DataSet dsdata = sqlConn.GetDataSetFromFactory(strQryy);
        //            Util.WritInfoLog("$ConfigData$", "GetXMasterListColumns", "End Query for GetXMasterListColumns");

        //            //do for paging 
        //            DataTable dtData = dsdata.Tables[0];
        //            dtData.Columns.Remove("Row");
        //            dtData.Columns.Remove("USER_TYPE_ID");

        //            int cnt = 0;
        //            cnt = Convert.ToInt32(dsdata.Tables[1].Rows[0]["CNT"]);
        //            strData = Common.JsonForJqgrid(dtData, rows, cnt, page);
        //        }

        //        JavaScriptSerializer js = new JavaScriptSerializer();
        //        return strData;            

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog("$ConfigData$", "get_UserData", "Error in get_UserData", "", ex);
        //        throw ex;
        //    }
        //}

        public string addUserType()
        {
            try
            {

                Connection sqlConn = new Connection();        
                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@USER_TYPE_NAME", DbType.String, ParameterDirection.Input, 255, allkeys["USER_TYPE_NAME"]));

                Util.WritInfoLog("$ConfigData$", "addUserType", "method for ifExist in addUserType", string.Format("method:{0}", "usp_IfExistsUserType"));
                DataSet dsMax = sqlConn.GetDataSetFromFactoryProc("usp_IfExistsUserType", ref(lstparam));
                Util.WritInfoLog("$ConfigData$", "addUserType", "End method for ifExist in addUserType");

                if (Convert.ToInt16(dsMax.Tables[0].Rows[0]["CNT"]) > 0)
                {
                    return "false| User Type Name should be unique !";
                }
                else
                {

                    List<dict> allkey = new List<dict>();

                    foreach (string key in allkeys)
                    {
                        dict d = new dict();
                        d.key1 = key;
                        d.value = allkeys[key];
                        allkey.Add(d);
                    }

                    var xmlDocument = new XmlDocument();
                    var serializer = new XmlSerializer(typeof(List<dict>));

                    using (var stream = new MemoryStream())
                    {
                        serializer.Serialize(stream, allkey);
                        stream.Flush(); stream.Seek(0, SeekOrigin.Begin);
                        xmlDocument.Load(stream);
                    }

                    List<DBParameter> lstparam1 = new List<DBParameter>();
                    lstparam1.Add(new DBParameter("@field_Xml", DbType.Xml, ParameterDirection.Input, 0, xmlDocument.OuterXml));

                    Util.WritInfoLog(ClsName, "addUserType", "method for addUserType", "usp_addUserType");
                    Int32 res = Convert.ToInt32(sqlConn.SaveDataByFactoryByProc("usp_addUserType", ref(lstparam1)));
                    Util.WritInfoLog(ClsName, "addUserType", "End method for addUserType");

                    if (res == 0) return "false";
                    else return Convert.ToString(res);  
                  
                }
            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "addUserType", "Error in addUserType", "", ex);
                throw ex;
            }
        }

        //before SP
        //public string addUserType()
        //{
        //    try
        //    {

        //        Connection sqlConn = new Connection();

        //        //get max + 1  id -1
        //        string strQry = " SELECT MAX(USER_TYPE_ID)+ 1 AS ID FROM M_USER_TYPE";

        //        strQry = strQry + " SELECT COUNT(*) CNT FROM M_USER_TYPE WHERE USER_TYPE_NAME='";
        //        strQry = strQry + allkeys["USER_TYPE_NAME"] + "'";

        //        Util.WritInfoLog("$ConfigData$", "addUserType", "Query for getting details in addUserType", string.Format("Query:{0}", strQry));
        //        DataSet dsMax = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog("$ConfigData$", "addUserType", "End Query for getting details in addUserType");

        //        if (Convert.ToInt16(dsMax.Tables[1].Rows[0]["CNT"]) > 0)
        //        {
        //            return "false| User Type Name should be unique !";
        //        }
        //        else
        //        {
        //            string strInsert = "INSERT INTO  M_USER_TYPE";

        //            string colNm = string.Empty;
        //            string colVl = string.Empty;

        //            foreach (string key in allkeys)
        //            {

        //                if (key != "id" && key != "oper" && key != "hasUser")
        //                {
        //                    colNm = colNm + key + ",";
        //                    if (key == "USER_TYPE_NAME")
        //                    {
        //                        colVl = colVl + "'" + allkeys["USER_TYPE_NAME"] + "',";
        //                    }
        //                    else
        //                    {
        //                        if (allkeys[key].ToUpper() == "NO")
        //                        {
        //                            colVl = colVl + "'False',";
        //                        }
        //                        else
        //                        {
        //                            colVl = colVl + "'True'" + ",";
        //                        }
        //                    }
        //                }
        //            }

        //            colNm = colNm.Substring(0, colNm.Length - 1);
        //            colVl = colVl.Substring(0, colVl.Length - 1);

        //            strInsert = strInsert + "(USER_TYPE_ID," + colNm + ")";
        //            strInsert = strInsert + "VALUES (" + dsMax.Tables[0].Rows[0][0].ToString() + "," + colVl + ")";

        //            JavaScriptSerializer js = new JavaScriptSerializer();

        //            Util.WritInfoLog("$ConfigData$", "addUserType", "Query for addUserType", string.Format("Query:{0}", strInsert));
        //            Int32 res = sqlConn.SaveDataByFactory(strInsert);
        //            Util.WritInfoLog("$ConfigData$", "addUserType", "End Query for addUserType");

        //            if (res == 1) return dsMax.Tables[0].Rows[0][0].ToString();
        //            else return "false";
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog("$ConfigData$", "addUserType", "Error in addUserType", "", ex);
        //        throw ex;
        //    }
        //}

        //with SP

        public string editUserType()
        {
            try
            {

                Connection sqlConn = new Connection();

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@USER_TYPE_NAME", DbType.String, ParameterDirection.Input, 255, allkeys["USER_TYPE_NAME"]));
                lstparam.Add(new DBParameter("@USER_TYPE_ID", DbType.Int32, ParameterDirection.Input, 0, allkeys["id"]));

                Util.WritInfoLog("$ConfigData$", "editUserType", "method for ifExist in editUserType", string.Format("method:{0}", "usp_IfExistsUserType"));
                DataSet dsMax = sqlConn.GetDataSetFromFactoryProc("usp_IfExistsUserType", ref(lstparam));
                Util.WritInfoLog("$ConfigData$", "editUserType", "End method for ifExist in editUserType");

                if (Convert.ToInt16(dsMax.Tables[0].Rows[0]["CNT"]) > 0)
                {
                    return "false| User Type Name should be unique !";
                }
                else
                {

                    List<dict> allkey = new List<dict>();

                    foreach (string key in allkeys)
                    {
                        dict d = new dict();
                        d.key1 = key;
                        d.value = allkeys[key];
                        allkey.Add(d);
                    }

                    var xmlDocument = new XmlDocument();
                    var serializer = new XmlSerializer(typeof(List<dict>));

                    using (var stream = new MemoryStream())
                    {
                        serializer.Serialize(stream, allkey);
                        stream.Flush(); stream.Seek(0, SeekOrigin.Begin);
                        xmlDocument.Load(stream);
                    }

                    List<DBParameter> lstparam1 = new List<DBParameter>();
                    lstparam1.Add(new DBParameter("@field_Xml", DbType.Xml, ParameterDirection.Input, 0, xmlDocument.OuterXml));
                    lstparam1.Add(new DBParameter("@USER_TYPE_ID", DbType.Int32, ParameterDirection.Input, 0, allkeys["id"]));

                    Util.WritInfoLog(ClsName, "editUserType", "method for editUserType", "usp_editUserType");
                    Int32 res = Convert.ToInt32(sqlConn.SaveDataByFactoryByProc("usp_editUserType", ref(lstparam1)));
                    Util.WritInfoLog(ClsName, "editUserType", "End method for editUserType");

                    return "true";                
                }

            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "editUserType", "Error in editUserType", "", ex);
                throw ex;
            }
        }

        //before SP
        //public string editUserType()
        //{
        //    try
        //    {

        //        Connection sqlConn = new Connection();

        //        //get max + 1  id -1
        //        string strQry = " SELECT COUNT(*) CNT FROM M_USER_TYPE WHERE USER_TYPE_NAME='";
        //        strQry = strQry + allkeys["USER_TYPE_NAME"] + "'";
        //        strQry = strQry + " AND USER_TYPE_ID !=" + allkeys["id"];

        //        Util.WritInfoLog("$ConfigData$", "editUserType", "Query for getting details in editUserType", string.Format("Query:{0}", strQry));
        //        DataSet dsMax = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog("$ConfigData$", "editUserType", "End Query for getting details in editUserType");

        //        if (Convert.ToInt16(dsMax.Tables[0].Rows[0]["CNT"]) > 0)
        //        {
        //            return "false| User Type Name should be unique !";
        //        }
        //        else
        //        {

        //            string strUpdate = "UPDATE M_USER_TYPE SET ";

        //            string colNm = string.Empty;
        //            string colVl = string.Empty;

        //            foreach (string key in allkeys)
        //            {

        //                if (key != "id" && key != "oper" && key != "hasUser")
        //                {

        //                    colNm = colNm + key + ",";

        //                    if (key == "USER_TYPE_ID")
        //                    {
        //                        strUpdate = strUpdate + key + "=" + allkeys[key] + ",";
        //                    }
        //                    else if (key == "USER_TYPE_NAME")
        //                    {
        //                        strUpdate = strUpdate + key + "='" + allkeys[key] + "',";
        //                    }
        //                    else
        //                    {
        //                        if (allkeys[key].ToUpper() == "NO")
        //                        {
        //                            strUpdate = strUpdate + key + "='False',";
        //                        }
        //                        else
        //                        {
        //                            strUpdate = strUpdate + key + "='True',";
        //                        }
        //                    }
        //                }
        //            }

        //            strUpdate = strUpdate.Substring(0, strUpdate.Length - 1);
        //            strUpdate = strUpdate + " WHERE USER_TYPE_ID=" + allkeys["id"];

        //            JavaScriptSerializer js = new JavaScriptSerializer();

        //            Util.WritInfoLog("$ConfigData$", "editUserType", "Query for editUserType", string.Format("Query:{0}", strUpdate));
        //            Int32 res = sqlConn.SaveDataByFactory(strUpdate);
        //            Util.WritInfoLog("$ConfigData$", "editUserType", "End Query for editUserType");

        //            if (res == 1) return allkeys["id"];
        //            else return "false";
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog("$ConfigData$", "editUserType", "Error in editUserType", "", ex);
        //        throw ex;
        //    }
        //}

        public string delUserType()
        {
            try
            {

                Connection sqlConn = new Connection();

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@UserTypeId", DbType.Int32, ParameterDirection.Input, 0, allkeys["id"]));

                Util.WritInfoLog("$ConfigData$", "delUserType", "method for delUserType", string.Format("method:{0}", "usp_delUserType"));
                Int32 res = Convert.ToInt32(sqlConn.SaveDataByFactoryByProc("usp_delUserType", ref(lstparam)));
                Util.WritInfoLog("$ConfigData$", "delUserType", "End method for delUserType");

                return "true";
               // if (res == 1) return "true";
               // else return "false";
              
            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "delUserType", "Error in delUserType", "", ex);
                return "false";
                throw ex;
            }
        }

        //before SP
        //public string delUserType()
        //{
        //    try
        //    {

        //        Connection sqlConn = new Connection();

        //        string strDelQry = " DELETE FROM M_USER_TYPE ";
        //        strDelQry = strDelQry + " WHERE USER_TYPE_ID=" + allkeys["id"];

        //        JavaScriptSerializer js = new JavaScriptSerializer();

        //        Util.WritInfoLog("$ConfigData$", "delUserType", "Query for delUserType", string.Format("Query:{0}", strDelQry));
        //        int res = sqlConn.SaveDataByFactory(strDelQry);
        //        Util.WritInfoLog("$ConfigData$", "delUserType", "End Query for delUserType");


        //        if (res == 1) return "true";
        //        else return "false";

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog("$ConfigData$", "delUserType", "Error in delUserType", "", ex);
        //        throw ex;
        //    }
        //}

        public string addUser()
        {
            try
            {

                Connection sqlConn = new Connection();               
              
                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@LOGIN_NAME", DbType.String, ParameterDirection.Input, 255, allkeys["LOGIN_NAME"]));

                Util.WritInfoLog("$ConfigData$", "addUser", "method for ifExist in addUser", string.Format("method:{0}", "usp_IfExistsUser"));
                DataSet dsMax = sqlConn.GetDataSetFromFactoryProc("usp_IfExistsUser", ref(lstparam));
                Util.WritInfoLog("$ConfigData$", "addUser", "End method for ifExist in addUser");

                if (Convert.ToInt16(dsMax.Tables[0].Rows[0]["CNT"]) > 0)
                {
                    return "false| User Type Name should be unique !";
                }
                else
                {

                    List<dict> allkey = new List<dict>();

                    foreach (string key in allkeys)
                    {
                        dict d = new dict();
                        d.key1 = key;
                        d.value = allkeys[key];
                        allkey.Add(d);
                    }

                    var xmlDocument = new XmlDocument();
                    var serializer = new XmlSerializer(typeof(List<dict>));

                    using (var stream = new MemoryStream())
                    {
                        serializer.Serialize(stream, allkey);
                        stream.Flush(); stream.Seek(0, SeekOrigin.Begin);
                        xmlDocument.Load(stream);
                    }

                    List<DBParameter> lstparam1 = new List<DBParameter>();
                    lstparam1.Add(new DBParameter("@field_Xml", DbType.Xml, ParameterDirection.Input, 0, xmlDocument.OuterXml));
                    lstparam1.Add(new DBParameter("@USER_TYPE_ID", DbType.Int32, ParameterDirection.Input, 0,UserTypeId));

                    Util.WritInfoLog(ClsName, "addUserType", "method for addUser", "usp_addUser");
                    Int32 res = Convert.ToInt32(sqlConn.SaveDataByFactoryByProc("usp_addUser", ref(lstparam1)));
                    Util.WritInfoLog(ClsName, "addUserType", "End method for addUser");

                    if (res == 0) return "false";
                    else return Convert.ToString(res);                
                }
            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "addUser", "Error in addUser", "", ex);
                throw ex;
            }
        }

        //before SP
        //public string addUser()
        //{
        //    try
        //    {

        //        Connection sqlConn = new Connection();
        //        //Encryption encrypt = new Encryption();
        //        //get max + 1  id -1
        //        string strQry = " SELECT MAX(USER_ID)+ 1 AS ID FROM M_USER";

        //        strQry = strQry + " SELECT COUNT(*) CNT FROM M_USER WHERE LOGIN_NAME='";
        //        strQry = strQry + allkeys["LOGIN_NAME"] + "'";

        //        Util.WritInfoLog("$ConfigData$", "addUser", "Query for getting details in addUser", string.Format("Query:{0}", strQry));
        //        DataSet dsMax = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog("$ConfigData$", "addUser", "End Query for getting details in addUser");


        //        if (Convert.ToInt16(dsMax.Tables[1].Rows[0]["CNT"]) > 0)
        //        {
        //            return "false| User Name should be unique !";
        //        }
        //        else
        //        {

        //            string strInsert = "INSERT INTO M_USER";

        //            string colNm = string.Empty;
        //            string colVl = string.Empty;

        //            foreach (string key in allkeys)
        //            {

        //                if (key != "id" && key != "oper")
        //                {
        //                    colNm = colNm + key + ",";
        //                    if (key == "LOGIN_NAME")
        //                    {
        //                        colVl = colVl + "'" + allkeys["LOGIN_NAME"] + "',";
        //                    }
        //                    else if (key == "PASSWORD")
        //                    {
        //                        //encrypt password
        //                        //encrypt.GetEncryptedPassword(allkeys[key], "MD5");
        //                        //colVl = colVl + encrypt.GetEncryptedPassword(allkeys[key], "MD5") + ",";
        //                        //colVl = colVl + "Null" + ",";
        //                        colVl = colVl + "'" + allkeys[key] + "',";
        //                    }
        //                    else
        //                    {
        //                        if (allkeys[key].ToUpper() == "NO")
        //                        {
        //                            colVl = colVl + "'False',";
        //                        }
        //                        else
        //                        {
        //                            colVl = colVl + "'True'" + ",";
        //                        }
        //                    }
        //                }
        //            }

        //            colNm = colNm.Substring(0, colNm.Length - 1);
        //            colVl = colVl.Substring(0, colVl.Length - 1);

        //            strInsert = strInsert + "(USER_ID,USER_TYPE_ID," + colNm + ")";
        //            strInsert = strInsert + "VALUES (" + dsMax.Tables[0].Rows[0][0].ToString() + "," + UserTypeId + "," + colVl + ")";

        //            JavaScriptSerializer js = new JavaScriptSerializer();

        //            Util.WritInfoLog("$ConfigData$", "addUser", "Query for addUser", string.Format("Query:{0}", strInsert));
        //            Int32 res = sqlConn.SaveDataByFactory(strInsert);
        //            Util.WritInfoLog("$ConfigData$", "addUser", "End Query for addUser");


        //            if (res == 1) return dsMax.Tables[0].Rows[0][0].ToString();
        //            else return "false";
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog("$ConfigData$", "addUser", "Error in addUser", "", ex);
        //        throw ex;
        //    }
        //}

        public string editUser()
        {
            try
            {

                Connection sqlConn = new Connection();

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@LOGIN_NAME", DbType.String, ParameterDirection.Input, 255, allkeys["LOGIN_NAME"]));
                lstparam.Add(new DBParameter("@USER_ID", DbType.Int32, ParameterDirection.Input, 0, allkeys["id"]));

                Util.WritInfoLog("$ConfigData$", "editUser", "method for ifExist in editUser", string.Format("method:{0}", "usp_IfExistsUser"));
                DataSet dsMax = sqlConn.GetDataSetFromFactoryProc("usp_IfExistsUser", ref(lstparam));
                Util.WritInfoLog("$ConfigData$", "editUser", "End method for ifExist in editUser");

                if (Convert.ToInt16(dsMax.Tables[0].Rows[0]["CNT"]) > 0)
                {
                    return "false| User Name should be unique !";
                }
                else
                {

                    List<dict> allkey = new List<dict>();

                    foreach (string key in allkeys)
                    {
                        dict d = new dict();
                        d.key1 = key;
                        d.value = allkeys[key];
                        allkey.Add(d);
                    }

                    var xmlDocument = new XmlDocument();
                    var serializer = new XmlSerializer(typeof(List<dict>));

                    using (var stream = new MemoryStream())
                    {
                        serializer.Serialize(stream, allkey);
                        stream.Flush(); stream.Seek(0, SeekOrigin.Begin);
                        xmlDocument.Load(stream);
                    }

                    List<DBParameter> lstparam1 = new List<DBParameter>();
                    lstparam1.Add(new DBParameter("@field_Xml", DbType.Xml, ParameterDirection.Input, 0, xmlDocument.OuterXml));
                    lstparam1.Add(new DBParameter("@USER_ID", DbType.Int32, ParameterDirection.Input, 0, allkeys["id"]));

                    Util.WritInfoLog(ClsName, "editUser", "method for editUser", "usp_editUser");
                    Int32 res = Convert.ToInt32(sqlConn.SaveDataByFactoryByProc("usp_editUser", ref(lstparam1)));
                    Util.WritInfoLog(ClsName, "editUser", "End method for editUser");

                    return "true";      
                }

            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "editUser", "Error in editUser", "", ex);
                throw ex;
            }
        }

        //before SP
        //public string editUser()
        //{
        //    try
        //    {

        //        Connection sqlConn = new Connection();

        //        //get max + 1  id -1
        //        string strQry = " SELECT COUNT(*) CNT FROM M_USER WHERE LOGIN_NAME='";
        //        strQry = strQry + allkeys["LOGIN_NAME"] + "'";
        //        strQry = strQry + " AND USER_ID !=" + allkeys["id"];

        //        Util.WritInfoLog("$ConfigData$", "editUser", "Query for getting details in editUser", string.Format("Query:{0}", strQry));
        //        DataSet dsMax = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog("$ConfigData$", "editUser", "End Query for getting details in editUser");

        //        if (Convert.ToInt16(dsMax.Tables[0].Rows[0]["CNT"]) > 0)
        //        {
        //            return "false| User Name should be unique !";
        //        }
        //        else
        //        {

        //            string strUpdate = "UPDATE M_USER SET ";

        //            string colNm = string.Empty;
        //            string colVl = string.Empty;

        //            foreach (string key in allkeys)
        //            {

        //                if (key != "id" && key != "oper")
        //                {

        //                    colNm = colNm + key + ",";

        //                    if (key == "USER_ID")
        //                    {
        //                        strUpdate = strUpdate + key + "=" + allkeys[key] + ",";
        //                    }
        //                    else if (key == "LOGIN_NAME")
        //                    {
        //                        strUpdate = strUpdate + key + "='" + allkeys[key] + "',";
        //                    }
        //                    else if (key == "PASSWORD")
        //                    {
        //                        //encrypt password
        //                        strUpdate = strUpdate + key + "='" + allkeys[key] + "',";
        //                    }
        //                    else if (allkeys[key].ToUpper() == "NO" || allkeys[key].ToUpper() == "YES")
        //                    {
        //                        if (allkeys[key].ToUpper() == "NO")
        //                        {
        //                            strUpdate = strUpdate + key + "='False',";
        //                        }
        //                        else
        //                        {
        //                            strUpdate = strUpdate + key + "='True',";
        //                        }
        //                    }

        //                }
        //            }

        //            strUpdate = strUpdate.Substring(0, strUpdate.Length - 1);
        //            strUpdate = strUpdate + " WHERE USER_ID=" + allkeys["id"];

        //            JavaScriptSerializer js = new JavaScriptSerializer();

        //            Util.WritInfoLog("$ConfigData$", "editUser", "Query for editUser", string.Format("Query:{0}", strUpdate));
        //            Int32 res = sqlConn.SaveDataByFactory(strUpdate);
        //            Util.WritInfoLog("$ConfigData$", "editUser", "End Query for editUser");

        //            if (res == 1) return allkeys["id"];
        //            else return "false";
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog("$ConfigData$", "editUser", "Error in editUser", "", ex);
        //        throw ex;
        //    }
        //}

        public string delUser()
        {
            try
            {

                Connection sqlConn = new Connection();

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@UserId", DbType.Int32, ParameterDirection.Input, 0, allkeys["id"]));

                Util.WritInfoLog("$ConfigData$", "delUser", "method for delUser", string.Format("method:{0}", "usp_delUser"));
                Int32 res = Convert.ToInt32(sqlConn.SaveDataByFactoryByProc("usp_delUser", ref(lstparam)));
                Util.WritInfoLog("$ConfigData$", "delUser", "End method for delUser");

                return "true";
               // if (res == 1) return "true";
               // else return "false";
            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "delUser", "Error in delUser", "", ex);
                return "false";
                throw ex;
            }
        }

        public string get_SourceData()
        {
            try
            {

                string primarykey = string.Empty;

                Connection sqlConn = new Connection();

                Util.WritInfoLog("$ConfigData$", "get_SourceData", "method for get_SourceData", string.Format("Query:{0}", "usp_GetSourceData"));
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetSourceData");
                Util.WritInfoLog("$ConfigData$", "get_SourceData", "End method for get_SourceData");

                DataTable dt = ds.Tables[0];
                DataTable dtcol = ds.Tables[1];
                DataView dv = new DataView(dtcol);

                DataTable dtfilter = dv.ToTable();

                Grid griddata = new Grid();
                //set columns,colmodel,primary key
                griddata = SetColModel(griddata, dtfilter);

                List<object> rows = SetDatarow(dt);
                griddata.datarow = rows;

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(griddata);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog("$ConfigData$", "get_UserData", "Error in get_UserData", "", ex);
                throw ex;
            }
        }

        //before SP
        //public string delUser()
        //{
        //    try
        //    {

        //        Connection sqlConn = new Connection();

        //        string strDelQry = " UPDATE M_USER SET IS_ACTIVE='False'";
        //        strDelQry = strDelQry + " WHERE USER_ID=" + allkeys["id"];

        //        JavaScriptSerializer js = new JavaScriptSerializer();

        //        Util.WritInfoLog("$ConfigData$", "delUser", "Query for delUser", string.Format("Query:{0}", strDelQry));
        //        int res = sqlConn.SaveDataByFactory(strDelQry);
        //        Util.WritInfoLog("$ConfigData$", "delUser", "End Query for delUser");

        //        if (res == 1) return "true";
        //        else return "false";

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog("$ConfigData$", "delUser", "Error in delUser", "", ex);
        //        throw ex;
        //    }
        //}

        //before SP

        ///for source testing //       
        //public string get_SourceData()
        //{
        //    try
        //    {

        //        string primarykey = string.Empty;

        //        Connection sqlConn = new Connection();

        //        string strQry = " SELECT * FROM M_SOURCE ";
        //        strQry = strQry + " ORDER BY SOURCE_ID ASC ";

        //        strQry = strQry + "    SELECT sys.columns.name as DISPLAY, ";
        //        strQry = strQry + "     sys.columns.name as FIELD_NAME ,sys.types.name DATA_TYPE, ";
        //        strQry = strQry + "   sys.columns.max_length PRECISION,  ";
        //        strQry = strQry + "    IS_REQUIRED= CASE sys.columns.is_nullable WHEN 1 THEN 0  ELSE 1 END ";
        //        strQry = strQry + "   ,ISNULL(i.is_primary_key, 0)IS_PRIMARY,sys.columns.is_nullable IS_NULLABLE, ";
        //        strQry = strQry + "  sys.columns.is_identity FROM sys.columns  ";
        //        strQry = strQry + "   INNER JOIN sys.types ON sys.columns.system_type_id ";
        //        strQry = strQry + "   =sys.types.system_type_id ";
        //        strQry = strQry + "   LEFT OUTER JOIN    sys.index_columns ic ON ic.object_id ";
        //        strQry = strQry + "    = sys.columns.object_id  AND ic.column_id = sys.columns.column_id  ";
        //        strQry = strQry + "    LEFT OUTER JOIN     sys.indexes i ON ic.object_id = i.object_id ";
        //        strQry = strQry + "    AND ic.index_id = i.index_id  WHERE sys.columns.object_id ";
        //        strQry = strQry + "   = OBJECT_ID('M_SOURCE') ";
        //        strQry = strQry + " order by sys.columns.column_id ";

        //        Util.WritInfoLog("$ConfigData$", "get_SourceData", "Query for get_SourceData", string.Format("Query:{0}", strQry));
        //        DataSet ds = sqlConn.GetDataSetFromFactory(strQry);
        //        Util.WritInfoLog("$ConfigData$", "get_SourceData", "End Query for get_SourceData");


        //        DataTable dt = ds.Tables[0];
        //        DataTable dtcol = ds.Tables[1];
        //        DataView dv = new DataView(dtcol);

        //        DataTable dtfilter = dv.ToTable();

        //        Grid griddata = new Grid();
        //        //set columns,colmodel,primary key
        //        griddata = SetColModel(griddata, dtfilter);

        //        List<object> rows = SetDatarow(dt);
        //        griddata.datarow = rows;

        //        //Convert.ToString(dsExist.Tables[0].Rows[0]["CNT"]) + "#" +
        //        JavaScriptSerializer js = new JavaScriptSerializer();
        //        // return js.Serialize(griddata) + "|" + Convert.ToString(dsExist.Tables[0].Rows[0]["CNT"]);
        //        return js.Serialize(griddata);
        //        //return js.Serialize(griddata) + "," + dsExist.Tables[0].Rows[0]["CNT"];
        //        //return "true";

        //    }
        //    catch (Exception ex)
        //    {
        //        Util.WritErrorLog("$ConfigData$", "get_UserData", "Error in get_UserData", "", ex);
        //        throw ex;
        //    }
        //}
        /////

    }
}
